Help with workbook path

[Grandad]

Hi

I need to specify the path of the ActiveWorkbook so that it can be used
later in my code.

I have tried something like "ActiveWorkbook = ActiveWorkbook.Path" but I'm
only guessing, can anyone help with the correct code please.

 

[Tom Ogilvy]

Dim sPath as String
sPath = ActiveWorkbook.Path
msgbox sPath

sPath will then hold a string that contains the activeworkbook path.

If by specify, you mean you are trying to change it, that can only be done
with a SaveAs .

 

[Bob Phillips]

ActiveWorkbook.FullName

I agree it should be Path, but there we are.

 

[Bob Phillips]

I read your question as getting the workbook name, but seeing Tom's response
as re-reading your question, I am probably wrong.

As Tom says, both Path and Fullname are string properties, so you cannot set
the workbook to them (why would you want to?)

What you can do is save in a variable

myWbString = Activeworkbook.Path (or Fullname)

 

[Grandad]

Guys

Thanks for the info. This works OK when I open the workbook as a template,
as it already has a "Saved" path. However, if I open as new document from
the template, as it has not been previously saved does not have a path. I
know I could save it then, but the default location is different on most of
the PCs that need to use the file. The folder structure is the same on all
PCs but they start from different drive letters. Is there a way to overcome
this?

Thanks for all your help.
Mick

 

[Tom Ogilvy]

When a file is opened using New and specifying a template, then there is no
link established between the new file (which as you say, until saved has no
path) and the template.

You can get the drive letter for the Excel application with

Left(Application.path,1)

or the path with

sPath = Application.Path

you can get the drive letter and current default directory with

sPath = Curdir

? curdir
C:\Documents and Settings\togilvy\My Documents

You can use the scripting runtime to identify drives and examine directories
or use the Window API.

Perhaps from the above, you can formulate a strategy to identify the
location (find a drive that has a path of

<d>:\myfolders\back or \mydata directory below the folder with the excel
application as examples) and someone can give you the specific code you
need.