Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where Are Query Codes ? (Excel 2000)

    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 ?

  2. #2
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Are Query Codes ? (Excel 2000)

    John Walkenbach has some suggestions
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Where Are Query Codes ? (Excel 2000)

    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

    <pre>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</pre>


  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Are Query Codes ? (Excel 2000)

    Steve,

    Thanks. But how can I see the QueryTable object ?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Are Query Codes ? (Excel 2000)

    I haven't used it myself, but Jan Karel Pieterse's Query manager <!post=Here,348547>Here<!/post> might do what you need.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Where Are Query Codes ? (Excel 2000)

    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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Are Query Codes ? (Excel 2000)

    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.
    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Are Query Codes ? (Excel 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •