I am writing a Macro that does some stuff then saves the file as "NAME.txt"
but the directory that I am will change with each new file. How do I grab
the directory of the active workbook? I can grab the full file path
(ActiveWorkbook.FullName) but I can not convert this to the directory. Any
help?

=CELL("filename") will return the file's directory path.

Keep the quotes and filename. Odd function but it gives you the info you
need.

Dave
--
Brevity is the soul of wit.


"Mallasch" wrote:

I am writing a Macro that does some stuff then saves the file as "NAME.txt"
but the directory that I am will change with each new file. How do I grab
the directory of the active workbook? I can grab the full file path
(ActiveWorkbook.FullName) but I can not convert this to the directory. Any
help?

Another way.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub FindWhatFolder()
MsgBox WhatFolder(ThisWorkbook.Path)
End Sub

Function WhatFolder(strPath) As String
Dim FSO As Object
Dim oFold As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFold = FSO.GetFolder(strPath)
WhatFolder = oFold.Path
End Function
'--------------------


"Mallasch"

wrote in message
I am writing a Macro that does some stuff then saves the file as "NAME.txt"
but the directory that I am will change with each new file. How do I grab
the directory of the active workbook? I can grab the full file path
(ActiveWorkbook.FullName) but I can not convert this to the directory. Any
help?

That will actually tell you the name of drive/path/filename/sheet name that was
active when excel last calculated.

In this case, you'd want to specify a range:

=cell("Filename",a1)

===========
In code, you could use:

msgbox activeworkbook.path



Dave F wrote:

=CELL("filename") will return the file's directory path.

Keep the quotes and filename. Odd function but it gives you the info you
need.

Dave
--
Brevity is the soul of wit.

"Mallasch" wrote:

I am writing a Macro that does some stuff then saves the file as "NAME.txt"
but the directory that I am will change with each new file. How do I grab
the directory of the active workbook? I can grab the full file path
(ActiveWorkbook.FullName) but I can not convert this to the directory. Any
help?

--

Dave Peterson

Thanks. activeworkbook.path worked well. I was using
activeworkbook.fullname which gave me the filename that I didn't want...

"Dave Peterson" wrote:

That will actually tell you the name of drive/path/filename/sheet name that was
active when excel last calculated.

In this case, you'd want to specify a range:

=cell("Filename",a1)

===========
In code, you could use:

msgbox activeworkbook.path



Dave F wrote:

=CELL("filename") will return the file's directory path.

Keep the quotes and filename. Odd function but it gives you the info you
need.

Dave
--
Brevity is the soul of wit.

"Mallasch" wrote:

I am writing a Macro that does some stuff then saves the file as "NAME.txt"
but the directory that I am will change with each new file. How do I grab
the directory of the active workbook? I can grab the full file path
(ActiveWorkbook.FullName) but I can not convert this to the directory. Any
help?

--

Dave Peterson