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