Does querytables.add -require- activesheet

[effort1584]

My workbook will have between 20 and 200 sheets (situation dependent) with querytables added to each sheet. With that quantity I am hoping to not have to make each sheet active to get a query added. However, my testing so far shows it works every time the current query is referring to the active sheet and fails if it is not.

I have tried a couple of ways of referring to the sheet where I need to add the query.

Sheets(3).querytables.add
Sheets("shtName").querytables.add
dim a worksheet and set it using both examples above

Of course activesheet.querytables.add always works.

I am using Excel 2010, however I tested in Excel 2003 with the same result.

Is there another way to refer to the sheet to avoid using activesheet?

 

[pbornemeier]

Welcome to the Board!

I was able to add a query to the non-active sheet in Excel 2003 using the following format. Note that the worksheet name in the 'Destination' portion of the code must match the worksheet name in the 'With' portion of the code:

    Sheets("Sheet2").Select 'Ensuring active sheet is not the destination
    With Worksheets("Sheet3").QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=J:\AIM Data\B.XLS;DefaultDir=J:\AIM Data;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize" _
        ), Array( _
        "=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
        )), Destination:=Sheets("Sheet3").Range("A1"))
        .CommandText = Array( _
        "SELECT `Sheet1$`.A, `Sheet1$`.B, `Sheet1$`.C, `Sheet1$`.`'D'`, `Sheet1$`.E, `Sheet1$`.F, `Sheet1$`.G, `Sheet1$`.H, `Sheet1$`." _
        , _
        "I, `Sheet1$`.J, `Sheet1$`.K" & Chr(13) & "" & Chr(10) & "FROM `J:\L\B`.`Sheet1$` `Sheet1$`" _
        )

 

[effort1584]

Thank you, I will try that.