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)

I don't think that excel can know what the current drive/folder is in the
current explorer window. Lots of times I have several window explorer windows
running and each point at a different location.

But if you're lucky, you could use the folder from the file that you opened.

activeworkbook.path
or
thisworkbook.path



[email protected] wrote:
>
> 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)

--

Dave Peterson

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


<[email protected]> wrote in message
news:[email protected]...
> 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)
>

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)

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

[email protected] 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