bonzo1971
06-19-2013, 01:16 PM
Hi to everyone,
i'm a newbye I have a problem with the vba code to import data from a table in excel file access. Using Office 2010, but the launch of the code gives me an error:
(translate from Italian)
run-time error -2147217865 (80040e37) Microsoft Access Database engine cannot find object 'INCONTRI$A1:I108468.
Make sure the object exists and that its name and the path you typed BE CORRECTED.
HERE IS THE CODE:
Sub EXPORT2ACCESS()
Dim ultimariga As Long
Dim ultimacolonna As Integer
Dim foglio, finesel As String
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TOTALBET\TOTALBET_XML_DB.accdb"
dbWb = Application.ActiveWorkbook.FullName
For i = 1 To 4
ultimariga = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
ultimacolonna = Sheets(i).Cells(1, Sheets(i).Columns.Count).End(xlToLeft).Column
finesel = Application.ConvertFormula("R" & ultimariga & "C" & ultimacolonna, xlR1C1, xlA1, toAbsolute:=xlRelative)
foglio = Application.Sheets(i).Name
Set cn = CreateObject("ADODB.Connection")
Dim cmd As ADODB.Command
dsh = "[" & Application.Sheets(i).Name & "$A1:" & finesel & "]"
cn.Open strCon
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = "Delete * from " & foglio
cmd.ActiveConnection = cn.ConnectionString
cmd.Execute
With Sheets(i)
For x = 1 To ultimacolonna
campo = "[" & .Cells(1, x).Value & "]"
If x = 1 Then
stringa = campo
Else
stringa = stringa & ", " & campo
End If
Next x
End With
''Insert into a table called strsql = "INSERT INTO " & foglio & "(" & stringa & ") "
strsql = strsql & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
Debug.Print strsql
''Execute the statement
cn.Execute strsql
Next i
End Sub
the table "incontri" exists in my excel file and contain data. same sub worked for other sheets without problems and insert data in access table.
if necessary send me by private message and i'll send you link to download filesI'm going crazy to understand where the error is please help!
Thanks in advance to all the people who want to help me figure out where I'm wrong.
greetings
Vincent
i'm a newbye I have a problem with the vba code to import data from a table in excel file access. Using Office 2010, but the launch of the code gives me an error:
(translate from Italian)
run-time error -2147217865 (80040e37) Microsoft Access Database engine cannot find object 'INCONTRI$A1:I108468.
Make sure the object exists and that its name and the path you typed BE CORRECTED.
HERE IS THE CODE:
Sub EXPORT2ACCESS()
Dim ultimariga As Long
Dim ultimacolonna As Integer
Dim foglio, finesel As String
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TOTALBET\TOTALBET_XML_DB.accdb"
dbWb = Application.ActiveWorkbook.FullName
For i = 1 To 4
ultimariga = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
ultimacolonna = Sheets(i).Cells(1, Sheets(i).Columns.Count).End(xlToLeft).Column
finesel = Application.ConvertFormula("R" & ultimariga & "C" & ultimacolonna, xlR1C1, xlA1, toAbsolute:=xlRelative)
foglio = Application.Sheets(i).Name
Set cn = CreateObject("ADODB.Connection")
Dim cmd As ADODB.Command
dsh = "[" & Application.Sheets(i).Name & "$A1:" & finesel & "]"
cn.Open strCon
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = "Delete * from " & foglio
cmd.ActiveConnection = cn.ConnectionString
cmd.Execute
With Sheets(i)
For x = 1 To ultimacolonna
campo = "[" & .Cells(1, x).Value & "]"
If x = 1 Then
stringa = campo
Else
stringa = stringa & ", " & campo
End If
Next x
End With
''Insert into a table called strsql = "INSERT INTO " & foglio & "(" & stringa & ") "
strsql = strsql & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
Debug.Print strsql
''Execute the statement
cn.Execute strsql
Next i
End Sub
the table "incontri" exists in my excel file and contain data. same sub worked for other sheets without problems and insert data in access table.
if necessary send me by private message and i'll send you link to download filesI'm going crazy to understand where the error is please help!
Thanks in advance to all the people who want to help me figure out where I'm wrong.
greetings
Vincent