Can’t change reference to msquery source (XP)

Topic

New Reply

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?

Reply | Quote

Replies

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 = “C:Databases”
With ActiveSheet.QueryTables(1)
.CommandText = Replace(.CommandText, strOldPath, strNewPath)
.Connection = Replace(.Connection, strOldPath, strNewPath)
End With

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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!

Dim strOldPath As String
Dim strNewPath As String
strOldPath = "C:Excel"
strNewPath = "C:Databases"

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

Reply | Quote

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!

Dim strOldPath As String
Dim strNewPath As String
strOldPath = "C:Excel"
strNewPath = "C:Databases"

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

Reply | Quote

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 = “C:Databases”
With ActiveSheet.QueryTables(1)
.CommandText = Replace(.CommandText, strOldPath, strNewPath)
.Connection = Replace(.Connection, strOldPath, strNewPath)
End With

Reply | Quote

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…

Reply | Quote

I forgot to add that the macro in the utility can be started from the Tools menu (Tools, Query Manager).

Reply | Quote

I forgot to add that the macro in the utility can be started from the Tools menu (Tools, Query Manager).

Reply | Quote