user2054797 user2054797 - 1 year ago 215
SQL Question

Joining Excel Tables to SQL Server using VBA

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

Answer Source

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

    Set rst1 = Nothing
    Set cnn1 = Nothing
    Set listObj = Nothing
    Set ws = Nothing

End Sub
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download