VBA to open workbook in different folder, without knowing full path

[Richard_mcr]

Hello Again!

I'm looking for some VBA to open a worksheet in a folder from a workbook in a different folder, however the full path will vary depending on who's copied the files where. As an example, I have a group of folders which always maintain the same structure, but are copied into different parent folders. Therefor, I need to establish the file path of the main workbook, go up a folder level, and then select the known folder name and then the worksheet. I've tried to show it visually here:

As always, any help is much appreciated!

Thanks!

 

[rollis13]

Here is a snippet I use in these cases where 'LocalPath' would be your different local path; see if you can use it in your project:

Sub RetrieveLocalPath()
    Dim FilePath As Variant
    Dim PathOnly As String
    Dim LocalPath As String
    PathOnly = ThisWorkbook.Path                  'retrieve Main WB's path
    FilePath = Split(PathOnly, "\")
    ReDim Preserve FilePath(UBound(FilePath) - 1) 'shift up 1 subfolder (or more)
    LocalPath = Join(FilePath, "\") & "\.DWG"     'new local path
    Debug.Print LocalPath                         'for testing purposes, shows only in the Immediate panel
End Sub

 

[Farscape2020]

If you know the file structure, I was going to suggest:

Sub openWorkbook(workbookName As String)

    Dim sourceWorkbook As Workbook
    Dim folderPath As String
    Dim filePath As String
    
    folderPath = Application.ThisWorkbook.Path

'  The following assumes you would like to open the workbook to retrieve the worksheet

    Set sourceWorkbook = Workbooks.Open(Left(folderPath, InStrRev(folderPath, "\")) & ".DWG\" & workbookName)


End Sub