Re-writing ODBC connection string

[percy83]

Hi guys,

I have this code that has always worked for me in the past. It simply loops through all sheets looking for querytables and then re-writes the connection string. I think I initially had it done for Excel 2003 but it should also work for 2010 right?! The table is also a list object. Here is the code I am using:

Dim qt As QueryTableDim wks As Worksheet


Dim strNewFilePath As String
Dim strNewFileName As String


strNewFilePath = ThisWorkbook.Path
strNewFileName = ThisWorkbook.Name


MsgBox strNewFilePath & strNewFileName




For Each wks In ActiveWorkbook.Worksheets
For Each qt In wks.QueryTable


'MsgBox qt.Connection


With qt
    .Connection = Join$(Array("ODBC;DSN=Excel Files;DBQ=", _
    strNewFilePath & Application.PathSeparator & strNewFileName, _
    ";DefaultDir=", strNewFilePath, _
    ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"), vbNullString)


    .Refresh BackgroundQuery:=False


End With


Next qt
Next wks
Set qt = Nothing
Set wks = Nothing

Any ideas on what might be wrong?

Have a great week-end!
Per

 

[Andrew Poulsom]

Missing an s here?

For Each qt In wks.QueryTables

 

[percy83]

Hi Andrew and thanks for your reply,

Sorry, I must have missed that when pasting the code here. The "s" is there in my code. The code executes and loops fine but it seems like it doesnt find the query table at all. Could it be stored/defined as something else?

 

[Andrew Poulsom]

If it's a QueryTable contained in a ListObject, you probably need to loop around the ListObjects.

 

[percy83]

Thanks I got it worked out.

It was now stored as a workbook connection instead of a query table. I don't really know why but it could have something to do with the fact that the query itself is just from another sheet from the same workbook.

Thanks again for your help!