Where Are Query Codes ? (Excel 2000)

Topic

New Reply

I create a lot of links in Excel to an Access database. I need to move the location of the Access file, but don’t want to re-establish the link one sheet by one sheet. Where can I see the codes of the query so I can edit it directly ?

Reply | Quote

Replies

John Walkenbach has some suggestions

Reply | Quote

John Walkenbach has some suggestions

Reply | Quote

It is in the SQL property and the Connection property of the QueryTable object. I think you only need to change it in the SQL property, the connection seems to go with the SQL. Something like this (change the QueryTable number, the old and new path as desired, include the filename if that changes also)

Steve

Sub ChangeQueryPath()
    Dim sOldPath As String
    Dim sNewPath As String

    sOldPath = "C:My Folder"
    sNewPath = "D:A different Folder"

    With ActiveSheet.QueryTables(1)
        .Sql = Application.WorksheetFunction.Substitute(.Sql, sOldPath, sNewPath)
    End With
End Sub

Reply | Quote

Steve,

Thanks. But how can I see the QueryTable object ?

Reply | Quote

What do you mean by “see”? I don’t understand, The query table is the output you get

You can manually “see” some of the properties (and manipulate them) by right clicking and getting info or you can create a reference to it and see the porperties in the Locals window in VB, or change and view them via code.

You can try the Jan’s Query manager, though, like Legare, I have never used it.

Steve

Reply | Quote

What do you mean by “see”? I don’t understand, The query table is the output you get

You can manually “see” some of the properties (and manipulate them) by right clicking and getting info or you can create a reference to it and see the porperties in the Locals window in VB, or change and view them via code.

You can try the Jan’s Query manager, though, like Legare, I have never used it.

Steve

Reply | Quote

Steve,

Thanks. But how can I see the QueryTable object ?

Reply | Quote

It is in the SQL property and the Connection property of the QueryTable object. I think you only need to change it in the SQL property, the connection seems to go with the SQL. Something like this (change the QueryTable number, the old and new path as desired, include the filename if that changes also)

Steve

Sub ChangeQueryPath()
    Dim sOldPath As String
    Dim sNewPath As String

    sOldPath = "C:My Folder"
    sNewPath = "D:A different Folder"

    With ActiveSheet.QueryTables(1)
        .Sql = Application.WorksheetFunction.Substitute(.Sql, sOldPath, sNewPath)
    End With
End Sub

Reply | Quote

I haven’t used it myself, but Jan Karel Pieterse’s Query manager Here might do what you need.

Reply | Quote

I haven’t used it myself, but Jan Karel Pieterse’s Query manager Here might do what you need.

Reply | Quote

I am busy building a querymanager thatt should do the job, but I suspect with a lot of tables to change the path to my Flexfind utility will be easier.

Download from http://www.jkp-ads.com[/url%5D.
After installing flexfind, write down the full path to your database and press control-shift-H. Search for the path making sure you check the Objects checkbox.
Then in the bottom listbox select all objects found in which you wish to replace the path, enter the new path and hit the Replace button.

Reply | Quote

Great add-in… solved all my headaches !!! Thanks.

Reply | Quote

Great add-in… solved all my headaches !!! Thanks.

Reply | Quote