Joining Excel Tables to SQL Server using VBA

Question

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

maybe [this](http://stackoverflow.com/questions/3474137/how-to-export-data-from-excel-spreadsheet-to-sql-server-2008-table) can help you, also [this](http://support.microsoft.com/kb/321686) — , Aug 13 '13 at 10:54
Thanks Mehow, I was looking for a VBA solution that can use Excel tables without the Wizard as I am using SQL Server Express. — william-1066, Aug 13 '13 at 15:31

score 1 · Accepted Answer · answered Aug 13 '13 at 11:38

How about using the table address rather than the table name?

Option Explicit

Sub test()
Dim listObj As ListObject
Dim tableName As String
Dim HeaderRange As String
Dim DataRange As String
Dim ws As Worksheet
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim strSQL As String
Dim arrData() As Variant

'Ensure reference is set for ActiveX Data Objects X.y (eg 6.1)

    tableName = "TableName1"
    Set ws = Sheet1
    Set listObj = ws.ListObjects(tableName) 'Table Name

    'get range of Table
    HeaderRange = listObj.HeaderRowRange.Address
    DataRange = listObj.DataBodyRange.Address

    cnn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";"

    strSQL = "SELECT * FROM [" & ws.Name & "$" & Replace(DataRange, "$", "") & "];"
    rst1.Open strSQL, cnn1, adOpenStatic, adLockReadOnly

    arrData = rst1.GetRows

    rst1.Close
    cnn1.Close
    Set rst1 = Nothing
    Set cnn1 = Nothing
    Set listObj = Nothing
    Set ws = Nothing


End Sub

In my example I've only used the data without fields. You can use `listObj.Range.Address` instead of DataRange and change the HDR in `"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"` — , Aug 13 '13 at 15:37

Joining Excel Tables to SQL Server using VBA

1 Answers1