Importing Data From Excel Using ADO

[Dan_Dollar]

Hi! I'm attempting to write an Excel VBA macro that will import data from a closed Excel workbook. I found a procedure online at:

http://www.exceltip.com/st/Import_da...Excel/429.html

I tried the procedure at home and it worked fine, but when I do it at work, I receive this error:

Run-time error "-2147217900 (80040e14)':

[Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Here is the procedure, with the faulty code highlighted:

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
'   this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
'
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")    
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Sub
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
        vbExclamation, "Get data from closed workbook"
End Sub

I found this link http://www.prairiefyre.com/ciskb/def...p?id=37&Lang=1 that said this might be an invalid query for the reason that:

An ODBC Connection to an Excel document has a limited set of operations. The SQL Queries that can be executed on an Excel spreadsheet are limited to 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. If using a query that is more advanced, the 6160 will return the following error when testing the ODCB Connection:

TESTING ODBC CONNECTION...
CONNECTION TEST PASSED
TESTING QUERY: IF EXISTS (SELECT * FROM NAME) return 'TRUE'ELSE return 'FALSE'
TEST FAILED WITH ERROR:
ERROR [42000] [Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

If this is true, can I make a different connection besides ODBC that will allow me to run this procedure? Or can I edit the code in some way that I won't have to use this statement? Or how else can I access objects from a closed workbook?

Thanks a bunch!

Dan

[Leith Ross]

Hello Dan_Dollar,

I think you overlooked a key point of the post. You must set a reference to the ADO library in Visual Basic for Applications.

At the end of the post...

The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft
ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Sincerely,
Leith Ross

[Dan_Dollar]

Hi Leith -

Sorry, I should have pointed out that I had already done that - the reference that I've established is "Microsoft ActiveX Data Objects 2.8 Library" - I was thinking it might be the wrong library, but I tried it at home and the code worked...strange...

Thanks!

Dan

[Dan_Dollar]

I don't know if this rude or not, but...

BUMP!

[Leith Ross]

Hello Dan,

No, it isn't rude. It maybe as you suspected that your machine at work may not fully support the ADO/SQL commands you have in your code. Since it works everywhere except work, I feel it is a good possibility. You didn't mention what you are at home and at work as far as Windows versions and Office versions. That may yield a clue.

Sincerely,
Leith Ross

[Dan_Dollar]

Hi Leigh -

Thanks for the response - I'm pretty sure both my laptop at home and at work run XP and Excel 2003 - but it's not a huge deal, I figured out a different way to design my workbook for now.

I think I just need to find someone who is familiar with ODBC connections, so I can find out if that is the problem, and if it is, if there is a possibility of using a different connection.

Thanks again!

Dan