CODEWITHSUNDEEP
databaseexcelms-accessvba
meikse
meikse

Reputation: 121

Export data from Excel to Access - Error: Argument not Optional

I'm trying to export data from Excel 2010 to Access 2010 with a VBA code on my excel file when I press a button. I just want to export the data from "Water Quality" sheet to the "Water Quality" table on my database (In the excel file and access file are other sheets and tables).

My actual code is:

Sub Button14_Click()

' Exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim LastRow As Long

' Set cn = New ADODB.Connection

'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Documents and Settings\Administrador\Mis documentos\MonEAU\modelEAU Database V.2.accdb; " & _
    "Persist Security Info=False;"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Documents and Settings\Administrador\Mis documentos\MonEAU\modelEAU Database V.2.accdb"

' Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon

' Find LastRow in Col A into the Sheet1
LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

' Insert unto a table called Water_Quality
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO Water_Quality " _
       & "SELECT * FROM " & scn & ".[Sheet1$A5:L" & LastRow & "]"

' Execute the statement
cn.Execute strSQL

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

I can debug without any problem the code but when I run it, a run error appears: "The Microsoft Office Access database engine could not find the object 'Sheet1$A5:L10'. Make sure the object exists and that you spell its name and the path name correctly." It seems that there's a problem with the line cn.Execute strSQL.

I've checked the names and the path name as well, and I can't find where the problem is.

Any help to solve it would be greatly appreciated.

Upvotes: 0

Views: 2448

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

Here are a few examples for inserting all the data at once:

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\docs\test.accdb"

''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon

''Create a table called ATable
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "SELECT * INTO ATable " _
       & "FROM " & scn & ".[Sheet7$A1:C4]"

''Execute the statement
cn.Execute strSQL

''Insert into a table called ATable
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO ATable " _
       & "SELECT * FROM " & scn & ".[Sheet7$A1:C4]"

''Execute the statement
cn.Execute strSQL

''Insert into a table with no column header in Excel, 
''the fields are [afield],[atext],[another]
scn = "[Excel 8.0;HDR=NO;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO ATable ([afield],[atext],[another]) " _
       & "SELECT F1 As afield, F2 As AText, F3 As another FROM " _
       & scn & ".[Sheet7$A1:C4]"

''Execute the statement
cn.Execute strSQL

Upvotes: 4

Related Questions