Changing An Absolute Path To A Relative Path

**elgourmet** · 08-03-2010, 04:42 AM

hello

i am running a VBA script which updates cells of all files in a given folder:

   Dim myPath As String
   Dim objFso As Object
   Dim myFolder As Object, myFile   As Object
   myPath = Range("A19") 
   Set myFolder = objFso.GetFolder(myPath).Files
   For Each myFile In myFolder
      Set wb = Workbooks.Open(myFile)

in A19, i am defining the path: i.e. C:\Documents\Files\Test

however, this is an absolute path. let's say, the document which executes the macro is located in "Files", i just want to write "\Test", instead of entering the whole absolute path. Or let's say i want to update the cells in the folder above, i would like to write "..\"

does anyone have some hints or tips on how to achieve this?
any help would be very very much appreciated!

**Andrew-R** · 08-03-2010, 04:49 AM

The code

Thisworkbook.Path

Will return the file path of the workbook that is executing the code.

**elgourmet** · 08-03-2010, 05:05 AM

thanks!! exactly what i wanted. works like a charm

one more question though. how can i go one folder up?
..\Test works, but ..\ doesnt....

**Andrew-R** · 08-03-2010, 05:11 AM

Add a little function to find the last occurence of a character within a string ...

Function FindLast(ByVal StringToSearch, ByVal StringToFind As String) As Integer
Dim LastCount

LastCount = 0

While InStr(LastCount + 1, StringToSearch, StringToFind) > 0
  LastCount = InStr(LastCount + 1, StringToSearch, StringToFind)
Wend

FindLast = LastCount


End Function

Then use this to find the last back slash in the file path...

Dim LastSlash

LastSlash = FindLast(ThisWorkbook.Path, "\")

If LastSlash > 0 Then
  MsgBox Left(ThisWorkbook.Path, LastSlash)
Else
  MsgBox "Top level directory"
End If