|
Macro syntax to open file in current explorer folder
Pt 1 : MS Excel 97 SR-2
----------------------- I would like to use a macro code line starting Workbooks.OpenText FileName:=".\AmsAll-1.prn", _ to open the tab delimited file "AmsAll-1.prn" in the folder from which I launch the Excel application. However the ".\" path qualifier always reverts to the C: drive, not the K: mapped drive that I access all my project files from. It is inconvenient to navigate several levels down to the project from C:\ just to run this one application. The following does work : Workbooks.OpenText FileName:="k:.\AmsAll-1.prn", _ however then the macro is not compatible with other engineers (flame-nix of Three Crub - this is a pre-emptive stop bid to decline any flames because of my profession) who use these project files in different folders (on their C: drives). I did a little experiment and placed the following ahead of the OpenText function curDrive = Left(CurDir, 2) ChDrive (curDrive) curDrive = CurDir turns out that the very first call to CurDir returns "C:\Documents and Settings\....fubar.... I guess that is something configured to be the default startup folder for excel, no doubt changeable from tools-options. Even if changing this to k:\ would not overcome the problem below : Pt 2 : MS-Excel 2000 9.0.2720 ----------------------------- The engineer currently trying to use this worksheet runs the above version. On his PC the file is loaded in C:\AFUBAR\ Yet on his PC the following Workbooks.OpenText FileName:=".\AmsAll-1.prn", _ throws some or other error, when I really expected it to work ??? All reasonable offers accepted, Fred. 2006-01-10(Tue) |
Macro syntax to open file in current explorer folder
By "the folder from which I launch the Excel application" do you mean that
the code will be running in a workbook located in the same directory as the target file? If so, you could then just use ThisWorkbook.Path to get the location. Tim wrote in message oups.com... Pt 1 : MS Excel 97 SR-2 ----------------------- I would like to use a macro code line starting Workbooks.OpenText FileName:=".\AmsAll-1.prn", _ to open the tab delimited file "AmsAll-1.prn" in the folder from which I launch the Excel application. However the ".\" path qualifier always reverts to the C: drive, not the K: mapped drive that I access all my project files from. It is inconvenient to navigate several levels down to the project from C:\ just to run this one application. The following does work : Workbooks.OpenText FileName:="k:.\AmsAll-1.prn", _ however then the macro is not compatible with other engineers (flame-nix of Three Crub - this is a pre-emptive stop bid to decline any flames because of my profession) who use these project files in different folders (on their C: drives). I did a little experiment and placed the following ahead of the OpenText function curDrive = Left(CurDir, 2) ChDrive (curDrive) curDrive = CurDir turns out that the very first call to CurDir returns "C:\Documents and Settings\....fubar.... I guess that is something configured to be the default startup folder for excel, no doubt changeable from tools-options. Even if changing this to k:\ would not overcome the problem below : Pt 2 : MS-Excel 2000 9.0.2720 ----------------------------- The engineer currently trying to use this worksheet runs the above version. On his PC the file is loaded in C:\AFUBAR\ Yet on his PC the following Workbooks.OpenText FileName:=".\AmsAll-1.prn", _ throws some or other error, when I really expected it to work ??? All reasonable offers accepted, Fred. 2006-01-10(Tue) |
Solution: Macro syntax to open file in current explorer folder
Thanks Dave and Tim,
The following worked great on Excel 97, I trust it will also work on Excel 2000: Workbooks.OpenText FileName:=ActiveWorkbook.Path() & "\AmsAll-1.prn", _ While I am posting, is there a way for a macro to provide the two 'N' responses that are mandatory to complete the file close process (the first to close without saving, the second to discard the information copied to the clipboard). These are the statements that I use to close the prn file Windows("AmsAll-1.prn").Activate ActiveWorkbook.Close I checked the Close command, it has one optional argument (a file list), no other clues as to how to avoid the two interactive dialog boxes. Thanks, Fred. 2006-01-11(Wed) |
Solution: Macro syntax to open file in current explorer folder
If you want to discard any changes:
workbooks("AmsAll-1.prn").close savechanges:=false If you want to save the changes: application.displayalerts=false workbooks("AmsAll-1.prn").close savechanges:=true application.displayalerts=true wrote: Thanks Dave and Tim, The following worked great on Excel 97, I trust it will also work on Excel 2000: Workbooks.OpenText FileName:=ActiveWorkbook.Path() & "\AmsAll-1.prn", _ While I am posting, is there a way for a macro to provide the two 'N' responses that are mandatory to complete the file close process (the first to close without saving, the second to discard the information copied to the clipboard). These are the statements that I use to close the prn file Windows("AmsAll-1.prn").Activate ActiveWorkbook.Close I checked the Close command, it has one optional argument (a file list), no other clues as to how to avoid the two interactive dialog boxes. Thanks, Fred. 2006-01-11(Wed) -- Dave Peterson |
| All times are GMT +1. The time now is 11:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com