Have excel queries use current directory

[Guest]

I have a workbook with 6 excel saved queries and pivot tables based on an
Access Database. Each month I copy the entire contents of the folder to a new
folder (example DbPop200404Apr gets copied to DBPop200405May). The access
database gets updated and I want the excel pivot tables and charts to reflect
this data. The queries on each worksheet still relate back to the original,
even though I edit each DQY to reflect the new directory. I have to go each
worksheet and reconstruct the query.

How do you make excel automatically use the DQYs in the CURRENT directory
where the workbook resides? I would even be willing to write a macro or vb
code to accomplish this.

Example below I edit this to change DbData200409SEP to look at DbData200410OCT
but the worksheet will still point to the DQY in DbData200409SEP.


XLODBC
1
DSN=MS Access
Database;DBQ=F:\DATA\Projects\DbData200409SEP\DbData2k.mdb;DefaultDir=F:\DATA\Projects\DbData200409SEP\;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;
SELECT q12aMonthlyIssueSum.`Report Month`, q12aMonthlyIssueSum.Cases FROM
`F:\DATA\Projects\DbData200409SEP\ExpeditedFs2k`.q12aMonthlyIssueSum
q12aMonthlyIssueSum ORDER BY q12aMonthlyIssueSum.`Report Month` DESC


Thank You

 

[Guest]

why not have a directory call current? then save that to
DBPop200405May. or is that too simple?

-----Original Message-----
I have a workbook with 6 excel saved queries and pivot tables based on an
Access Database. Each month I copy the entire contents of the folder to a new
folder (example DbPop200404Apr gets copied to DBPop200405May). The access
database gets updated and I want the excel pivot tables and charts to reflect
this data. The queries on each worksheet still relate back to the original,
even though I edit each DQY to reflect the new directory. I have to go each
worksheet and reconstruct the query.

How do you make excel automatically use the DQYs in the CURRENT directory
where the workbook resides? I would even be willing to write a macro or vb
code to accomplish this.

Example below I edit this to change DbData200409SEP to look at DbData200410OCT
but the worksheet will still point to the DQY in DbData200409SEP.


XLODBC
1
DSN=MS Access
Database;DBQ=F:\DATA\Projects\DbData200409SEP\DbData2k.mdb ;DefaultDir=F:\DATA\Projects\DbData200409SEP\;DriverId=25;F
IL=MS
Access;MaxBufferSize=2048;PageTimeout=5;
SELECT q12aMonthlyIssueSum.`Report Month`,

q12aMonthlyIssueSum.Cases FROM

 

[Guest]

I do not understand what you mean. When I say current directory I mean the
directory in which the workbook is located, not a directory called current.

Thank you

 

[Guest]

The anonymous replier was suggesting that you create a directory called
"Current" and use that for the current month. At the end of the month make a
copy of that folder, ie "Copy of Current" and then rename that to name of the
completed month. Then you can continue to work with the "Current" folder for
the new month without changing the links.

James.

 

[Guest]

The problem with that is the new "completed" month is still pointing to
CURRENT and if the tables are refreshed will show data from Current month DB
instead of data stored in the "completed" month DB.
The month's access db & excel worksheet are moved to a public folder. When
moved I have to "relink" everything each month, instead of it just looking to
the folder it resides in.

Thanks

 

[Guest]

Well it now depends on the answer to the following question:

Are you putting previous months into different folder for archival purposes?

If yes, then break the link to the DB entirely so that the data can never be
refreshed again.

If no, then you must be still working with the previous months' data. So in
that case you need to rethink your data structure so that you don't do the
folder archiving. Maybe make some reports for the end of each month and
archive them.

James.

 

[Guest]

Thank you for your response, but it is not answering my initial question. All
I want to know is how can I have a query in a worksheet and have it reference
the directory it resides in at the moment, not where it was initially
created. I want to be able to set up a workbook with several queries linked
to a db, give it to someone else and still have it work if they copy
everything to a different folder.

 

[Nick Hodge]

Query paths within Excel are 'hard coded so if the *workbook* is moved it
should still refer to the original path of the db. If you move the
database, then that's a different story. You will have to re-build all
links.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Stephen Bullen]

Hi Pattioc,

All
I want to know is how can I have a query in a worksheet and have it reference
the directory it resides in at the moment, not where it was initially
created. I want to be able to set up a workbook with several queries linked
to a db, give it to someone else and still have it work if they copy
everything to a different folder.

In that case, you'll need some VBA that runs every time the workbook is opened,
to scan through all the query tables in the workbook and updates their
connection information. How well do you know VBA?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Stephen,

I know VBA Very well from within Access 97 & 2k. I am not very familiar
with the Excel 2k object(s) or how to manipulate excel using VBA.
Originally, I had wanted to have create the workbooks and sheets directly
from Access. Thus keeping it all within the database and not have to maintain
both the excel and access portion. Any help would be greatly appreciated.
Thanks
pattioc

 

[Stephen Bullen]

Hi Pattioc,

I know VBA Very well from within Access 97 & 2k. I am not very familiar
with the Excel 2k object(s) or how to manipulate excel using VBA.
Originally, I had wanted to have create the workbooks and sheets directly
from Access. Thus keeping it all within the database and not have to maintain
both the excel and access portion. Any help would be greatly appreciated.

OK, the objects we're interested in are QueryTable objects, which have a
Connection and CommandText properties (amongst many others!). The Connection
property is basically a database connection string, which will contain the path
to the database. The CommandText is the SQL, which might also contain the path!

So to update all the query tables in the workbook, you'd put this in the
ThisWorkbook code module:

Private Sub Workbook_Open()

Dim oSht As Worksheet
Dim oQT As QueryTable
Dim sPath As String
Dim sConnect As String
Dim sCommand As String

'The path where the workbook resides
sPath = ThisWorkbook.Path

'Loop through all the sheets
For Each oSht In ThisWorkbook.Worksheets

'Loop through all the Query Tables
For Each oQT In oSht.QueryTables

'Get the Query Table settings
sConnect = oQT.Connection
sCommand = oQT.CommandText

'Manipulate the strings to set the correct path
'(or just remove it from the CommandText)

'Update the query table
oQT.CommandText = sCommand
oQT.Connection = sConnect

'Refresh the query
oQT.Refresh
Next 'Query Table
Next 'Sheet

End Sub

Given that you know VBA, I left out the string handling to put the correct path
name in there. Note that when you create the Query Table, the MSQuery addin
creates really bad SQL (that includes the path of the database!), which you
might want to replace with something neater (that doesn't include the path -
it's not needed). If you do that when you first create the query table (by
clicking the 'SQL' button in MS Query), you won't need to update the CommandText
each time.

Hope that helps

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Stephen
Thank you so much will give this a try today.

Thank you