I have a code that is list files in directory. how can I address code to look into parent directory of current workbook directory? i want it to be independent wherever I place it. (first code addressing to the second one to read file from) Thanks.
....
Application.ScreenUpdating = False
ShowPDFs "C:\Test\Working\", ws
ws.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
------------------------------
Private Sub ShowPDFs(ByRef fsoPath.......
Just check that the file is not at root level:
....
Application.ScreenUpdating = False
ShowPDFs ThisWorkbook.Path & "\..", ws
ws.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
------------------------------
Private Sub ShowPDFs(ByRef fsoPath.......
The solution you want:
If your behavior is to open an excel window and then open your recent file, please note that you should not forget to add change Drive and then change Directory into your VBA code.
Cause the Excel always start with the default Directory even it's just open your recent file !
Then, these should be help.
Solution A: You don't need to get parent directory to do anything else.
Dim ThisWorkbookPath As String
Dim ThisWorkbookPathParts As Variant
ThisWorkbookPath = ThisWorkbook.Path
ThisWorkbookPathParts = Split(ThisWorkbookPath, _
Application.PathSeparator)
ChDrive ThisWorkbookPathParts(LBound(ThisWorkbookPathParts))
ChDir ThisWorkbookPath
Solution B: You may need to get parent directory to do things else.
Dim ParentPath As String: ParentPath = "\"
Dim ThisWorkbookPath As String
Dim ThisWorkbookPathParts, Part As Variant
Dim Count, Parts As Long
ThisWorkbookPath = ThisWorkbook.Path
ThisWorkbookPathParts = Split(ThisWorkbookPath, _
Application.PathSeparator)
Parts = UBound(ThisWorkbookPathParts)
Count = 0
For Each Part In ThisWorkbookPathParts
If Count > 0 Then
ParentPath = ParentPath & Part & "\"
End If
Count = Count + 1
If Count = Parts Then Exit For
Next
MsgBox "File-Drive = " & ThisWorkbookPathParts _
(LBound(ThisWorkbookPathParts))
MsgBox "Parent-Path = " & ParentPath
