Excel 2k3 query path to current directory
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. |
Excel 2k3 query path to current directory
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. |
All times are GMT +1. The time now is 10:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com