how to save an excel range to acces with this code

Question

how to save an excel range to acces with this code

Navigation

#1 by (2 votes)

2

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

cn.Close

Set cn = Nothing

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

vba excel ms-access-2007

asked by Marlon Camacho Polo 30.05.2017 в 17:03

source

1 answer

Get brand and model of the PC motherboard in java? [closed] Modify status: focus from HTML

score 2 · Accepted 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