Excel queryTable connection string query table missing? 1

[tchor7]

Looks like it cannot find my query tables??? below is an almost identical thread to this issue:

http://www.tek-tips.com/viewthread.cfm?qid=1099238

Many searches on the internet proposed Skips solution:

For Each ws In ThisWorkbook.Worksheets
ws.select
For Each qt In ws.QueryTables
qt.Connection = sConnection
Next
Next

It never gets to the qt.Connection line

When I test in the immediate window, I get an error:

Worksheets(1).QueryTables(1).count

I must be missing something very simple. Please advise.

Thanks,
Thoeum

 

[SkipVought]

Do you have 2007+ Excel?

If so...

dim lo as listobject

For Each ws In ThisWorkbook.Worksheets
    ws.select
    For Each lo In ws.listobjects
        lo.querytable.Connection = sConnection
    Next
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tchor7]

Hi Skip - thanks for the reply. It did loop through, but now getting error: 1004

My code:

sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name

sConnection = "DSN=Excel Files;DBQ=" & sPath & "\" & sDB & ";DefaultDir=" _
& sPath & "\" _
& ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

For Each ws In ThisWorkbook.Worksheets
ws.Select
For Each lo In ws.ListObjects
lo.QueryTable.Connection = sConnection
Next
Next

current queryTable connection string:

DSN=Excel Files;DBQ=H:\QueryTest.xlsm;DefaultDir=H:\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

sConnection value under "watch window":

"DSN=Excel Files;DBQ=C:\Documents and Settings\tc074h\Desktop\Book1.xlsx;DefaultDir=C:\Documents and Settings\tc074h\Desktop\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

Thanks,
Thoeum

 

[SkipVought]

You do not want a \ after the path in assgning the DefaultDir...

    sConnection = "DSN=Excel Files;"
    sConnection = sConnection & "DBQ=" & sPath & "\" & sDB & ";"
    sConnection = sConnection & "DefaultDir=" & sPath & ";"
    sConnection = sConnection & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tchor7]

Thanks Skip - I made the changes you suggested. Just had use the replace() fn and it works like a charm! much thanks.

lo.QueryTable.Connection = Replace(lo.QueryTable.Connection, stOldConnection, sConnection)

-Thoeum