Excel ADODB connection for SQL runtime error

Question

I was trying to use SQL queries to work with my excel sheets as tables. I wrote the following code:

Global objConn As ADODB.Connection
Global ConnString As String 
Global SQL As String
Global objRS As ADODB.Recordset
Global masterFile As String

Public Sub XL_DB_connect()

Set objConn = New ADODB.Connection

masterFile = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name

ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & masterFile & ";" & _
             "Extended Properties=""Excel 12.0;" & _
             "HDR=Yes;"";"

objConn.ConnectionString = ConnString  
objConn.Open

End Sub


Public Sub executeQuery()

objRS.Source = SQL
objRS.ActiveConnection = objConn   
objRS.Open

End Sub



Public Sub XL_DB_relMem()

Set objRS = Nothing
Set objConn = Nothing
SQL = vbNullString

End Sub


Public Sub test()

Set objRS = New ADODB.Recordset
objRS.CursorLocation = adUseClient

SQL = "select PatientGID, count(LOT) from [Sheet1$] group by PatientGID"

Debug.Print SQL
Call XL_DB_connect
Call executeQuery

objRS.MoveFirst

Range("Output").Resize(10, 2).ClearContents
Range("Output").CopyFromRecordset objRS  

End Sub

Sheet1 has the following columns starting from cell A1 patientGID
progression loT newLoT
loTFdate
actualRegimen
loTRegimenClass progressionClass
pERMetFlag

On running the code, I get following error:

"Run-time error '-2147467259 (80004005)'

'Sheet1$' is not a valid name. Make sure that it does not include invalid characters and punctuation and that it is not too long"

This is fine & does what you think, you would only see this error if the sheet was not actually called Sheet1, are you sure it is? (Your test leaved the connection open btw) — Alex K.
I am sure it's called that. Is there any other thing that I might be doing wrong ? — Krithi07
It works for me as expected (Excel 2016). Try renaming the sheet in both places and see what happens. — Alex K.

Kke Kke · Accepted Answer · 2019-08-29T08:52:13

I got the same error today. I found that the reason was the excel file was in its read-only status. When I saved it in another location, the error was immediately fixed.

Excel ADODB connection for SQL runtime error

1 Answers