i wonder that get the parent folder of folder of current excel file
using macro.

ex) working directory : c:\aa\bb
answer : c:\aa



*** Sent via Developersdex http://www.developersdex.com ***

"tom taol" wrote in message
...
: i wonder that get the parent folder of folder of current excel file
: using macro.
:
: ex) working directory : c:\aa\bb
: answer : c:\aa
:
Use the GetParentFolderName Method of the FileSystemObject. See MSDN link
below
http://tinyurl.com/afbgz
Paul D

Sub GetParent()
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
MsgBox oFSO.GetFile(ActiveWorkbook.FullName).ParentFolder .ParentFolder.Path
End Sub


--
HTH

Bob Phillips

"tom taol" wrote in message
...
i wonder that get the parent folder of folder of current excel file
using macro.

ex) working directory : c:\aa\bb
answer : c:\aa



*** Sent via Developersdex http://www.developersdex.com ***

Tom,
How about:
Folders = Split(ThisWorkbook.Path, "\")
MsgBox Folders(UBound(Folders) - 1)

Need error checking in the situation of "C:\MyFile.xls".

NickHK

"tom taol" wrote in message
...
i wonder that get the parent folder of folder of current excel file
using macro.

ex) working directory : c:\aa\bb
answer : c:\aa



*** Sent via Developersdex http://www.developersdex.com ***

"NickHK" wrote in message
...

Need error checking in the situation of "C:\MyFile.xls".

Mine also fails for root files, so also needs error handling.

Mine also fails for root files, so also needs error handling.

Here's a slight change that may help with root files.

Sub GetParent()
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")

With oFSO.GetFile(ActiveWorkbook.FullName).ParentFolder
If .IsRootFolder Then
'No Parent Folder
Debug.Print .Path
Else
Debug.Print .ParentFolder.Path
End If
End With
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Bob Phillips" wrote in message
...


"NickHK" wrote in message
...

Need error checking in the situation of "C:\MyFile.xls".

Mine also fails for root files, so also needs error handling.

I could have (should have?) done that myself, but I guess I am too lazy.

Thanks Dana.

Bob

"Dana DeLouis" wrote in message
...
Mine also fails for root files, so also needs error handling.

Here's a slight change that may help with root files.

Sub GetParent()
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")

With oFSO.GetFile(ActiveWorkbook.FullName).ParentFolder
If .IsRootFolder Then
'No Parent Folder
Debug.Print .Path
Else
Debug.Print .ParentFolder.Path
End If
End With
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Bob Phillips" wrote in message
...


"NickHK" wrote in message
...

Need error checking in the situation of "C:\MyFile.xls".

Mine also fails for root files, so also needs error handling.