Need directory path without filename

  • Thread starter Thread starter shilparvind
  • Start date Start date
S

shilparvind

Hi,

Is there a way to find out only the directory path without filename
using VBA macro. I came across macros that will spit out only filename
from the full path.

Example: D:\Documents\ExcelPrograms\

Any help on this will be greatly appreciated.

Regards
Shilpa
 
Use the Path property of the Workbook object.

Debug.Print ThisWorkbook.Path


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
easy :)
msgbox activeworkbook.path
or
msgbox thisworkbook.path
or
workbooks("myworkbook.xls").path
if it returns null then it has not yet been saved
ben
 
Hi Shilpa,

Is there a way to find out only the directory path without filename
using VBA macro. I came across macros that will spit out only filename
from the full path.

Example: D:\Documents\ExcelPrograms\
Click to expand...

If you're looking for an open workbook's path, then take Chip's advice and
use the Path property. If you have a full path/filename string and want the
path only, you could use the FileSystemObject:

Public Function gsGetPath(rsFilePath As String) As String
Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(rsFilePath) Then
gsGetPath = fso.GetFile(rsFilePath).ParentFolder.Path & _
Application.PathSeparator
End If

Set fso = Nothing
End Function

Alternatively, you could use InStrRev to find the last occurence of the
backslash, then use Left$() to get the path from there.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top