MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Getting directory of current workbook (curdir does not do this)

  Asked By: Abelerd    Date: Mar 02    Category: MS Office    Views: 1978

I'm sure that others have noticed that the CurDir() function defaults
to the "My Documents" directory, instead of the directory of the
currently open workbook.

In Access VBA I have always used "CurrentProject.Path" to get the
actual current path. This gives an error in Excel VBA.
Any suggestions?



3 Answers Found

Answer #1    Answered By: Kanchan Ap     Answered On: Mar 02

I use


However this gives an empty string if the workbook  is a new one which has not
been saved.

Note that this does not include a trailing "\" so you need to add this if you
are constructing a path  and filename string.

Answer #2    Answered By: Haya Yoshida     Answered On: Mar 02

ThisWorkbook.Path returns the path  of the workbook  containing that code.

ActiveWorkbook.Path returns the path of the active workbook.

If the workbook has not yet been saved, no path is returned.

Answer #3    Answered By: Geneva Morris     Answered On: Mar 02

Try this

Private Sub ABC( )
Dim posn As Integer, i As Integer
Dim flName As String
Dim fPath As String ' path  of file

flName = ThisWorkbook. FullName

posn = 0

For i = 1 To Len(flName)
If (Mid(flName, i, 1) = "\") Then posn = i
Next i

fPath = Left(flName, posn - 1)
msgbox fpath

End Sub