how to save an excel range to acces with this code


I have this excel vba code

Dim cs As String
Dim sPath As String
Dim sql As String
Dim sql2 As String
Dim cn As ADODB.Connection

sPath = ThisWorkbook.Path & "\datos.accdb"
cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"

Set cn = New ADODB.Connection
cn.Open cs

sql = "insert into tabla1 (nombre, apellido) values('" & Cells(1, 1).Value & "', '" & Cells(1, 2).Value & "')"

cn.Execute sql


Set cn = Nothing

this code only allows me to copy a specific cell, which I must modify so that I select a range

asked by Marlon Camacho Polo 30.05.2017 в 17:03

1 answer


You can not make an insert in a table of ranges, unless they come from another select.

In this case, you should loop through all the cells you want to enter, and execute the insert for each new cursor position.

something like this (not tested):

for i = 1 to NN
    sql = "insert into tabla1 (nombre, apellido) values('" & Cells(i, 1).Value & "', '" & Cells(i, 2).Value & "')"
    cn.Execute sql
next i
answered by 30.05.2017 / 17:41