database query redirect?

[percy83]

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!

 

[Fazza]

In essence, a query table has

• .Connection
• .SQL (aka .CommandText)

Although when manually created the SQL contains the file path, typically this is not required. So, remove it and then only the connection needs to change (to suit a different source file). Instead of SQL like
"SELECT * FROM `hard coded path`.tablename" changed it to
"SELECT * FROM tablename"

Once this is done, to change the query to suit a new file can be like below. Excel 2003, please modify to suit. Untested.

A great alternative to query tables is ADO. There are lots of sites with good info; I'll search for some good links and post again.

HTH, Fazza

dim qt as querytable
dim wks as worksheet
 
dim strNewFilePath as string
dim strNewFileName as string
 
strNewFilePath = "c:\test"
strNewFileName = "MyFile.xls"
 
for each wks in activeworkbook.worksheets
for each qt in wks.querytables
 
qt.connection = join$(array("ODBC;DSN=Excel Files;DBQ=", _
strNewFilePath & Application.PathSeparator & strNewFileName, _
";DefaultDir=", strNewFilePath, _
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"),vbnullstring)
 
qt.refresh backgroundquery:=false
 
next qt
next wks
set qt=nothing
set wks=nothing

 

[Fazza]

Some good links from http://www.mrexcel.com/forum/showthread.php?t=332472

 

[percy83]

Thank you all for your help! <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’ve been looking around and I haven’t found any really “basic excel user” user friendly solution. <o></o>
<o></o>
So for now I think I will stick to having a set filepath/name in my queries. The problem I have now is how to change my existing queries (referring to a source file on my homefolder H: to be able to move this excel project to a network drive. <o></o>
<o></o>
Where exactly do I need to change the connection string and do I need to also move the query files (dqy)? <o></o>
<o></o>
Thanks again!<o></o>
<o></o>

 

[xenou]

Maybe we still need to see your query.

1) Activate the worksheet with the Query Table.
2) Go to VBEditor (ALT-F11).
3) In the immediate window - usually at the bottom - hit CTRL-G if its not visible - type:

?activesheet.querytables(1).connection

4) Report the result

5) Then do the same, typing instead:

?activesheet.querytables(1).CommandText

6) Report the result

Or perhaps, Record adding your query to a new book with the macro recorder and post the whole query (as Andy did with his example).

Alex
Recording macros
Debugging/Using the Immediate Window

-----------------------------
Note: if this is a one time shot, you could just add the query again to the workbook. As far as dqy files, your query and connection information is normally stored in the workbook and you don't need to worry about it - as far as I can remember anyway.

 

[percy83]

Maybe we still need to see your query.

1) Activate the worksheet with the Query Table.
2) Go to VBEditor (ALT-F11).
3) In the immediate window - usually at the bottom - hit CTRL-G if its not visible - type:

?activesheet.querytables(1).connection

4) Report the result

5) Then do the same, typing instead:

?activesheet.querytables(1).CommandText

6) Report the result

Or perhaps, Record adding your query to a new book with the macro recorder and post the whole query (as Andy did with his example).

Alex
Recording macros
Debugging/Using the Immediate Window

-----------------------------
Note: if this is a one time shot, you could just add the query again to the workbook. As far as dqy files, your query and connection information is normally stored in the workbook and you don't need to worry about it - as far as I can remember anyway.

These are the results I got:

ODBC;DSN=Excel Files;DBQ=H:\My Documents\Masterlistan\Masterlistan v3\Källfiler\DeliveryTimePlanReport v3.xls;DefaultDir=H:\My Documents\Masterlistan\Masterlistan v3\Källfiler;DriverId=790;MaxBufferSize=2048;PageTimeout=5;
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
<o></o>
FROM `DeliveryTimePlanReport$` `DeliveryTimePlanReport$`<o></o>
WHERE (`DeliveryTimePlanReport$`.`Store code`<>'08W03<o></o>
08W02' And `DeliveryTimePlanReport$`.`Store code`<>'Delivery OK | Done/Ready' And `DeliveryTimePlanReport$`.`Store code`<>'Not to be delivered | Not to be done' And `DeliveryTimePlanReport$`.`Store code`<>'Store planning | Construction') AND (`DeliveryTimePlanReport$`.`Proj# action`<>'Closure') AND (`DeliveryTimePlanReport$`.`Proj# status`<>'R')<o></o>
ORDER BY `DeliveryTimePlanReport$`.`Store code`<o></o>
<o></o>

 

[xenou]

Okay, this is not complicated but be careful and backup first.
Do the same thing in the immediate window. But this time we are going to make a change in the connection string:

First, do the same step above to get the connection string:

?activesheet.querytables(1).connection

Having done that, we want to do the following:

1) Put your cursor at the end of the first line (the one you typed with the question mark), and Hit the Delete key, Type a space, equal sign, and space, and double quote mark.

2) Also put a double quote mark at the end.

3) You should have something like:

?activesheet.querytables(1).connection [COLOR="Blue"][B]= "[/B][/COLOR]ODBC;DSN=Excel Files;DBQ=H:\...more stuff...[COLOR="blue"][B]"[/B][/COLOR]

4) Now, very simply, where you see the path to your file, change it to the path to the new file. Be sure to change the full path, everything between DBQ= and the ending semi-colon, where you put the filepath, and everything between DefaultDir= and the ending semi-colon where you put the folder path.

5) last but not least, delete the question mark at the beginning of the line. This will change the statement from something you are "asking" Excel (what's my connection string?) to something you are "telling" Excel (this is my connection string!).

6)Hit enter, and Excel will change the connection string to everything between the quotes.

Its probably no harder to do this in the interface as described by Andy in post #2 a while back...although I'm so used to working in the VB environment that I would probably do this as described above. We haven't yet determined how you are going to do this on an ad hoc, frequent basis, but again, andy was on the right track in his first replies, as was Fazza with his example of a query that uses a variable for the filename in the connection string.

Alex

 

[Fazza]

In case it was missed, I posted code above to change the connections on all query tables in the workbook. So, just load, edit the inputs of data file path and name and run. hth

 

[xenou]

Thanks Fazza. You know I learned all this from you anyway.

 

[Fazza]

Well, we all learn from each other. Learning is so much easier with the internet & forums like this one.