I would like to be able to load data from an Excel Worksheet to a SQL server database.
I am able to do this in VBA one row at a time using loops, but it would be great if I could go further and also do joins. The code posted by Remou on this form here looks ideal, but I can't get it working.
Where I think I am stumped is getting VBA to recognize a table correctly in excel. In the code Remou provided there is a join on simply [Sheet2$]; here I keep getting 'Invalid object name' errors no matter how I try to define my Excel data. Ideally the array I would like to do a join with would be defined as a table in excel.
What is needed in VBA to recognize a table for use in a join?
Any advice/tips greatly appreciated.
Dim cnTrans As New ADODB.Connection
''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s