How can you make an EXCEL workbook look for the MS queries (xxx1.DQY) in the
SAME folder in which the workbook and the Access db reside.

The Excel workbook has 6 tabs with pivot tables or query results. This means
there are separate MS queries in each to run corresponding Access queries to
retrieve data from the Access Db. Each month I copy and rename this folder
to run the next month's data. I go in change the directory specified in the
DQY code.

If I just refresh the tab it will run the query against the prior month or
say it can't find it in a specific drive. I have to edit each through the
wizard to erase the old and select the new. I also copy the results to the
network drive which means doing the same editing.
Why can't excel have a setting for the current location path for each
workbook instead of a general default path.

How can I have the workbook/worksheet look in the new folder automatically.

As a worksheet function, in any cell on any sheet:
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)
this will return the full path of a workbook that has been saved (when you
first put the formula into a new workbook as Book1.xls, it shows nothing
until after saving and reopen, after that it stays current).

In VBA code:
Sub FindMe()
Dim PathToMe As String
PathToMe = Left(ThisWorkbook.FullName, _
Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name))
MsgBox PathToMe
End Sub

Same restriction - nothing returned untill after the workbook has been saved.


"busypgmr" wrote:

How can you make an EXCEL workbook look for the MS queries (xxx1.DQY) in the
SAME folder in which the workbook and the Access db reside.

The Excel workbook has 6 tabs with pivot tables or query results. This means
there are separate MS queries in each to run corresponding Access queries to
retrieve data from the Access Db. Each month I copy and rename this folder
to run the next month's data. I go in change the directory specified in the
DQY code.

If I just refresh the tab it will run the query against the prior month or
say it can't find it in a specific drive. I have to edit each through the
wizard to erase the old and select the new. I also copy the results to the
network drive which means doing the same editing.
Why can't excel have a setting for the current location path for each
workbook instead of a general default path.

How can I have the workbook/worksheet look in the new folder automatically.