Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't change reference to msquery source (XP)

    I created a query to import data from an external database file (the file is local on my pc). After creating the query, I moved the file to a different directory. How do I get Excel or MsQuery to use the database file in the new location as the new source when updating?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't change reference to msquery source (XP)

    1. Interactively, it is convoluted and illogical (anyone know of a better method?)

    Open the workbook.
    Click somewhere in the imported data.
    Click "Edit Query" on the External Data toolbar, or select Data | Import External Data | Edit Query...
    You'll get an error message "Could not find file ..."
    Click OK.
    You'll get a login screen. Click on Database...
    Browse to the new location of the database, select it and click OK twice.
    You'll get another error message "This query can not be edited...". Click OK.
    You'll get the error message "Could not find file ..." again (!), then Microsoft Query opens.
    Click the SQL button, or select View | SQL.
    Edit the location of the database in the SQL string manually, then click OK.
    Select File | Return data to Excel.
    Phew!

    2. In VBA:

    Dim strOldPath As String
    Dim strNewPath As String
    strOldPath = "C:Excel"
    strNewPath = "Catabases"
    With ActiveSheet.QueryTables(1)
    .CommandText = Replace(.CommandText, strOldPath, strNewPath)
    .Connection = Replace(.Connection, strOldPath, strNewPath)
    End With

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

    Re: Can't change reference to msquery source (XP)

    You can also use my flexfind (see my website below) to S&R in the query definition.
    Another option is to try this little utility...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't change reference to msquery source (XP)

    Thank You Hans

    I couldn't get the first part to work but the VBA worked like a bomb.

    Why did MS made it so difficult?

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

    Re: Can't change reference to msquery source (XP)

    I forgot to add that the macro in the utility can be started from the Tools menu (Tools, Query Manager).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't change reference to msquery source (XP)

    Ah, the beauty of the Search function! My situation was similar, but for multiple MSQuery query tables on multiple worksheets. I surrounded the code from Hans with the below and it worked wonderfully -- cheers! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <pre>Dim strOldPath As String
    Dim strNewPath As String
    strOldPath = "C:Excel"
    strNewPath = "Catabases"

    Dim wSht As Worksheet
    Dim allwShts As Sheets
    Set allwShts = Worksheets
    Dim strName As String

    'Loop through each worksheet
    For Each wSht In allwShts
    wSht.Activate

    For Each QueryTable In ActiveSheet.QueryTables

    strName = QueryTable.Name
    With ActiveSheet.QueryTables(strName)
    .CommandText = Replace(.CommandText, strOldPath, strNewPath)
    .Connection = Replace(.Connection, strOldPath, strNewPath)
    End With
    MsgBox strName & "Complete"

    Next

    Next wSht

    MsgBox "Every MSQuery has been changed.", vbInformation</pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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