Change SQL Command in Excel Connection

Question

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!

Alex K. · Accepted Answer · 2012-02-07 14:05:26Z

2

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

answered Feb 7, 2012 at 14:05

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ositra Over a year ago

It still does not work. "Runtime error '9': Subscript out of range"

Collectives™ on Stack Overflow

Change SQL Command in Excel Connection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related