PDA

View Full Version : Solved: Need the Root Directory of Thisworkbook



frank_m
02-03-2012, 12:00 PM
Edit: I apologize for changing the request
I actually need this to also work if the Root Directory is a UNC Path
ie: \\MySharedDrive

I need help getting the Root Directory of Thisworkbook
MyDir = Root Directory of ThisWorkbook & "\Blue Prints\"
'ie: D:\Blue Prints\ or \\MySharedDrive\Blue Prints\
Thanks

Kenneth Hobs
02-03-2012, 01:42 PM
ThisWorkbook.Path

Sub t()
MsgBox GetRootDrive(ThisWorkbook.path)
End Sub

Function GetRootDrive(Optional aPath As String) As String
' include reference to MS Scripting
Dim FSO As FileSystemObject, s As String
Set FSO = New FileSystemObject
'GetRootFolder = FSO.GetSpecialFolder(WindowsFolder)
s = FSO.GetDriveName(aPath)
Set FSO = Nothing
GetRootDrive = s
End Function

frank_m
02-03-2012, 05:43 PM
Thanks Kenneth, that's what I needed to know :thumb
Appreciate your help :friends:

Just adapted to late binding to simplify distribution to the users.

Sub t()
MsgBox GetRootDrive(ThisWorkbook.path)
End Sub

Function GetRootDrive(Optional aPath As String) As String
Dim FSO As Object, s As String
Set FSO = CreateObject("Scripting.FileSystemObject")
s = FSO.GetDriveName(aPath)
Set FSO = Nothing
GetRootDrive = s
End Function

Bob Phillips
02-04-2012, 04:01 AM
You can do that in one line Frank



Function GetRootDrive(Optional aPath As String) As String
GetRootDrive = CreateObject("Scripting.FileSystemObject").GetDriveName(aPath)
End Function