How to get the current path

[Stefan Mueller]

I've created a shortcut of an Excel sheet (e.g. Example.xls). On the
shortcut I do a right mouse click and then click properties. In 'Start
in:' I type 'C:\Temp'. Now I double click the shortcut 'Example.xls'
and like to get the current path (in this example 'C:\Temp').

In real VB I do it with a FileListBox:
MsgBox (FileListBox1.Path)

But how can I do it in VBA (e.g. Excel)?

 

[Guest]

Stefan,

You can use the CurDir function to get the current directory..

 

[Gary Keramidas]

activeworkbook.Path

 

[Stefan Mueller]

Thanks for your reply.

CurDir is not bad but it doesn't really care what is written in 'Start
in:' resp. from which path I've startet the Excel workbook.

If I start the Excel workbook e.g. from the Desktop it should show 'C:
\Documents and Settings\Administrator\Desktop'.
If in 'Start in:' of the shortcut of the Excel workbook is written 'C:
\Temp' it should show 'C:\Temp'.

 

[Stefan Mueller]

ActiveWorkbook.Path is closer to my wanted function than CurDir
because it shows e.g. 'C:\Documents and Settings\Administrator
\Desktop' if I start the Excel workbook from the Desktop.
However, it doesn't show 'C:\Temp' if in 'Start in:' of the shortcut
of the Excel workbook is written 'C:\Temp'.

 

[Gary Keramidas]

if i open a workbook from c:\temp it returns c:\temp

from the immediate window

?activeworkbook.Path
C:\Temp

 

[Dave Peterson]

I don't think that the workbook file itself is in C:\temp. The workbook could
be anywhere. But there's a shortcut on the desktop that points to the correct
location.

But inside that shortcut (rightclick on it and choose properties, then Shortcut
tab) you'll see another spot to specify a "start in" folder.

I have no idea how to get that information based on the opened workbook.

 

[Stefan Mueller]

Like already mentioned, in real VB the FileListBox (FileListBox1.Path)
shows this information.
Unfortunately there's no FileListBox in VBA. Therefore I'm looking for
another way to get this information.

ActiveWorkbook.Path is fine if you start the real Excel sheet. But it
does not work if you start the Excel sheet via a shotcut. In that case
ActiveWorkbook.Path still shows the path where the real Excel sheet is
located and not where the shortcut is located resp. what is defined in
'Start in:'.

 

[NickHK]

"Start In" in a short cut can point to any valid location, but it seems to
be ignored by Excel, if indeed Excel is even told about that setting,
possibly via DDE if anything.
Excel would seems to read from .DefaultFilePath instead upon start up.

Also I don't see how VB5/6's FileListBox exposes the StartIn parameter of a
short cut.

If you make a simple VB5/6 app with only a command button:

Private Sub Command1_Click()
MsgBox CurDir
End Sub

and start the app with various shortcut pointing to different StartIn
folders, you can see the changes.

Excel does not seem to work like this though.
Out of curiousity, why does it matter ?

NickHK

 

[Dave Peterson]

I still don't know how to get that path.

But if you know the shortcut name (including location), you could read the
shortcut and find out.

 

[Stefan Mueller]

Hello Dave

Thanks for your hint but I'm still hoping that someone knows a way to
get the path entered in 'Start in:'.

 

[NickHK]

I don't think Excel ever knows it, so consequently cannot tell you.
From an Excel point of view, why does it matter as it does seem to affect
Excel.

NickHK

 

[Stefan Mueller]

Hmm, but how can the VB's FileListBox do it?
I'm trying to find a way to get this information because I'd like to
open another file from the same directory I've started the Excel file
resp. Excel shortcut.

 

[NickHK]

I don't see how a VB FileListbox helps. How can that control tell you the
location of the short cut that open a file ?
Show your VB code that does that.

NickHK

 

[Stefan Mueller]

You understood me wrong.
I just want to mention that in real VB I can get the information
entered in 'Start in:' of a shortcut with a FileListBox:
MsgBox (FileListBox.Path)

Unfortunately VBA doesn't have a FileListBox and therefore I'm looking
for another way to get this information.

 

[NickHK]

Stefan,
I still don't see how "MsgBox (FileListBox.Path)" tells you anything about
the StartIn setting of a short cut, but this may help:
http://www.vbaccelerator.com/home/Vb/Code/Libraries/Shell_Projects/Shortcuts/article.asp

NickHK

 

[Stefan Mueller]

Hi NickHK

Thanks a lot for your replies and your time. I appreciate it very
much, but I think we are still not talking about the same.

In real Visual Basic (I'm using VB6) I add a FileListBox (e.g.
FileListBox1) to the Form I'd like to know the current path (directory
where the EXE file has been started or the path which is entered in
'Start in:' if a shortcut of the EXE file has been started).

Normally I hide the FileListBox with
FileListBox1.Visible = False
FileListBox1.ZOrder 1

In the Form_Load I read the current path:
Private Sub Form_Load()
...
CurrentPath = FileListBox1.Path
...
End Sub

Now CurrentPath represents either the directory where the EXE file has
been started or the path which is entered in 'Start in:' if a shortcut
of the EXE file has been started.
That's the way I do it in real VB and that works perfect.

Because VBA (e.g. Excel) doesn't now the FileListBox control I'm
trying to get the same information somehow else.

To get the directory where the XLS file has been started I can do in
VBA with
ActiveWorkbook.Path

But I don't know how to get in VBA the path which is entered in 'Start
in:' of the shortcut of the started shortcut of the XLS file.

 

[NickHK]

Compile a VB6 app that only contains one form and these lines:
Private Sub Form_Load()
MsgBox CurDir
End Sub

Create a shortcut to the app somewhere and set the StartIn to anything you
want. Click the short cut.
No need for the FileListBox.

AFAIK
The command line to the app is being told to set it's CurDir to the StartIn
value.
However, when you open an Excel file (which is not an executable), there is
no associtaed command line and hence no where for the StartIn parameter to
go.
Therefore Excel never knows about it.

NickHK

 

[Stefan Mueller]

Hi NickHK

You are absolutely right!
In VB6 the function CurDir does exactly what I tried to do with a
hidden FileListBox.
Many thanks for that hint and sorry for my confusion. I'm still a
beginner ;-)

I don't exactly know which path the function CurDir in VBA (e.g.
Excel) shows. I just now it's not the path entered in 'Start In:'.
If I understand you right: there is no possibility to read the path
entered in 'Start In:' within VBA because Excel doesn't know how it
(the worksheet) has been started (direct or via shortcut) because it's
not an executable.
Is this correct?

Many thanks
Stefan

 

[NickHK]

Stefan,
Yes, that's what I understand.

You could put some code in the Workbook_Open event of the workbook in
question to set CurDir, but this obviously will not be linked to the StartIn
parameter of the short cut.

NickHK