CODEWITHSUNDEEP
sqlexcelvba
ositra
ositra

Reputation: 165

Change SQL Command in Excel Connection

I am working with Excel 2010. I need to create a macro, that after entering text in a specific cell, changes the SQL Command text of a connection to a database correspondingly to the entered text. After executing a refresh the newest information from the database must be displayed.

I'm having a problem with changing the SQL command text. The code below - "Set qTable.." line to be exact gives me the "Runtime error 9 subscript out of range" error.

Dim qTable As QueryTable
Set qTable = Workbooks("Dynamic_Reports").Sheets("Report").ListObjects(1).QueryTables(1)
qTable.commandText = commandText
qTable.Refresh

"Dynamic_Reports" is the name of the Excel file. "Report" is the name of the worksheet, where the information has to be updated.

I've found similar posts about the error above, but none of them speak of QueryTable properties. I'm looking forward to your answers!

Upvotes: 2

Views: 2264

Answers (1)

Alex K.
Alex K.

Reputation: 175766

Looking in the object browser a ListObject has a single QueryTable rather than the QueryTables collection so;

Set qTable = Workbooks("Dynamic_Reports").Sheets("Report").ListObjects(1).QueryTable

Upvotes: 2

Related Questions