database query redirect?

percy83

Active Member
Joined
Mar 11, 2009
Messages
278
Hi Excel experts,

I’ve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine.
What I can’t figure out is how to re-direct my query to any file/path I choose.

At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it won’t work.

Thanks a bunch for any kind of help I could get on this one!

Have a great week-end!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Not sure if this is sufficient for what you want but if you edit the query then click the SQL button you will be able to amend the file path where it appears in the script.
 
Upvote 0
Ok, that is one way. Does anybody know if there is an easier/ more user freindly way to fix this issue?

Regards
Per
 
Upvote 0
Why is your data source (file) changing? Typically, you want a data source to be stable. What is the changing file all about?
Regards,
Alex
 
Upvote 0
Hi Alexander,

My database source is a report from a project planning tool (another programme) so its always changing. What my excelfile is doing is that it takes all input from this planning tool to calculate an "ideal delivery time plan" so it can be used for my purchase dept.

Lets say that I download a fresh copy of the report I want to use once a week and I dont wan't to replace (with the same name and filepath) the old file but to simply redirect my existing query to this new file so I can keep the old one for backtracking.

I hope this makes any sense!

Thanks for your help!
Best regards

Per
 
Upvote 0
OK I have a solution, you will have to set up a macro as follows for each of your Query’s:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Firstly record a macro of you editing the query but don’t actually make any changes just select File>Return Data to Microsoft Office Excel then stop the macro recording. You should end up with something like this but with your own Query script in there:<o:p></o:p>
<o:p></o:p>
Sub AmendQueryFilePath()<o:p></o:p>
Range("A1").Select<o:p></o:p>
With ActiveWorkbook.Connections("Query from Excel Files").ODBCConnection<o:p></o:p>
.BackgroundQuery = True<o:p></o:p>
.CommandText = Array( _<o:p></o:p>
"SELECT `Sheet1$`.Col1, `Sheet1$`.Col2, `Sheet1$`.Col3, `Sheet1$`.Col4" & Chr(13) & "" & Chr(10) & "FROM `C:\testquery.xls`.`Sheet1$` `Sheet1$`")<o:p></o:p>
.CommandType = xlCmdSql<o:p></o:p>
.Connection = Array(Array( _<o:p></o:p>
"ODBC;DSN=Excel Files;DBQ=C:\testquery.xls;DefaultDir=C:\Documents and Settings\abrierley\My Documents;DriverId=1046;MaxBufferSize=20"), Array_("48;PageTimeout=5;"))<o:p></o:p>
.RefreshOnFileOpen = False<o:p></o:p>
.SavePassword = False<o:p></o:p>
.SourceConnectionFile = ""<o:p></o:p>
.SourceDataFile = ""<o:p></o:p>
.ServerCredentialsMethod = xlCredentialsMethodIntegrated<o:p></o:p>
.AlwaysUseConnectionFile = False<o:p></o:p>
End With<o:p></o:p>
With ActiveWorkbook.Connections("Query from Excel Files")<o:p></o:p>
.Name = "Query from Excel Files"<o:p></o:p>
.Description = ""<o:p></o:p>
End With<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
You then need to edit the macro as follows:<o:p></o:p>
<o:p></o:p>
1. Insert the additional 2 lines at the beginning of your macro as shown in red:<o:p></o:p>
<o:p></o:p>
Sub AmendQueryFilePath()<o:p></o:p>
Dim MyPath As String<o:p></o:p>
MyPath = Worksheets("Sheet2").Cells.Item(1, 7)<o:p></o:p>
Range("A1").Select<o:p></o:p>
<o:p></o:p>
Note: the Worksheets(“Sheet2”).Cells.Item(1,7) relates to the location Row 1 Column 7 in Sheet2, this location can be amended and is the cell you will need to place you file path inc. filename (eg C:\testquery.xls)<o:p></o:p>
<o:p></o:p>
2. There will be 2 instances of your file path (C:\testquery.xls) in the macro which you need to replace with the following, being careful to include the quotes “ and the spaces before and after the & signs::<o:p></o:p>
<o:p></o:p>
" & MyPath & "<o:p></o:p>
<o:p></o:p>
In the above example the relevant lines would become:<o:p></o:p>
<o:p></o:p>
..."SELECT `Sheet1$`.Col1, `Sheet1$`.Col2, `Sheet1$`.Col3, `Sheet1$`.Col4" & Chr(13) & "" & Chr(10) & "FROM `" & MyPath & "<o:p></o:p>`.`Sheet1$` `Sheet1$`")<o:p></o:p>………<o:p></o:p>
<o:p></o:p>
and<o:p></o:p>
<o:p></o:p>
"ODBC;DSN=Excel Files;DBQ=" & MyPath & "<o:p></o:p>;DefaultDir=C:\Documents and Settings\abrierley\My Documents;DriverId=1046;MaxBufferSize=20"), Array_ ........
<o:p></o:p>
When done just enter the new file path in the cell you chose in step 1 and run the macro.<o:p></o:p>
<o:p></o:p>
You will then have to refresh the query data to see the results.<o:p></o:p>
<o:p></o:p>
Hope this helps!<o:p></o:p>
 
Upvote 0
The above post is on target...you only need to change the connection string, specifically that portion of it that contains the filepath - which can be accomplished with vba. Is this making any sense?

For what its worth, you could back up the file from the previous week with the new name, and then you could leave the file that's being queried with the same name (and the new data) - something that requires no VBA.

Alex.
 
Upvote 0
The above post is on target...you only need to change the connection string, specifically that portion of it that contains the filepath - which can be accomplished with vba. Is this making any sense?

For what its worth, you could back up the file from the previous week with the new name, and then you could leave the file that's being queried with the same name (and the new data) - something that requires no VBA.

Alex.

Hi Alexander,

I thought the whole point of the original question was having to avoid copying the new file over the old? Also I am puzzled how you achieve the result by changing only the connection string and not the file path in the Select Query itself bearing in mind the query is only pointing to another Excel file. If you have any VBA code for this I would be most grateful.

Obviously the down side with my macro is that it would have to be edited or re-recorded for any changes made in the query. I would be most interested if this could be avoided

Many Thanks

Andy B
 
Upvote 0
I am a total noob at these sort of excel functions and I wonder if I set up my queries as efficiant as they could be. When I created the file containing my query the first time I just chose Data-->Import External data-->New database query and then the existing Excel file option.

Would it have been better to have added a source to be able to change it more easily? What I am looking for is the most efficiant way to set up my queries so that I can later on put all my excelfiles in this project on a network drive where it will be accessed by my whole department.

Thanks for helping guys!

P.

Buy the way. Do you guys have any useful links where I can read up on this functionality?
Thanks again.
 
Last edited:
Upvote 0
Hi Percy,

I'm probably being somewhat indirect but if possible you want your data source to be stable, so that it doesn't need to be changed "on the fly". The same holds true for your queries - if possible, write them so that the user can simple "run the query" without worrying about "where the data is". Maybe this isn't possible. Maybe it is.

You may be interested in parameterized queries, as that is one way to add some dynamism to your queries. See: Parameters-daily dose of Excel and Parameters - Tushar Mehta

As far as sites for using queries in Excel - I think the above two are very good so if you don't need parameters just ignore that part and learn from the rest. This following site almost works, but veers towards a focus on SQL server and loses the rest (I can't decide if its helpful or not. What do you think?): MS Online, External Data However, there's a book by Zapawa is an excellent resource overall on this.

Keep talking about your ideas and requirements as I'm sorting of trying to picture your scenario and think perhaps different solutions are possible here...

----------------------------------

Hi Andy,

You're right, we'd need to change this string in two places. As far as the point being "to avoid copying the new file over the old", yes, I guess am sort of turning things on their head here.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top