Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

The workbook 'TasksTodayAA.xlsm' is in the folder 'Premises Staff Schedules' 'Premises…

  1. User

    The workbook 'TasksTodayAA.xlsm' is in the folder 'Premises Staff Schedules' 'Premises Staff Schedules' is in the folder 'zzzz ServProvDocs' 'zzzz ServProvDocs' is in the folder 'SERVICE PROVIDERS' The workbook 'Service Providers.xlsm' is in the folder 'SERVICE PROVIDERS' How can I create a Relative Path in my workbook 'Service Providers.xlsm' to the workbook 'TasksTodayAA.xlsm'

  2. Assistant

    To create a relative path in your workbook 'Service Providers.xlsm' to the workbook 'TasksTodayAA.xlsm', you need to calculate the relative path based on the folder structure.

    Assuming both workbooks are in the same parent folder 'SERVICE PROVIDERS', you can use the following relative path:

    '../Premises Staff Schedules/TasksTodayAA.xlsm'

    Explanation:

    • '..' signifies moving up one level in the folder hierarchy.
    • 'Premises Staff Schedules' is the subfolder where 'TasksTodayAA.xlsm' is located.
    • '/TasksTodayAA.xlsm' specifies the file name.

    Make sure to adjust the relative path if the folder structure is different or if the workbooks are located in different parent folders.

  3. User

    The folder 'Premises Staff Schedules' is in a folder 'zzzz ServProvDocs' which is in the folder 'SERVICE PROVIDERS'

  4. Assistant

    Apologies for the confusion. Based on the revised folder structure, you can create the following relative path in your workbook 'Service Providers.xlsm' to the workbook 'TasksTodayAA.xlsm':

    '../zzzz ServProvDocs/Premises Staff Schedules/TasksTodayAA.xlsm'

    Explanation:

    • '..' signifies moving up one level in the folder hierarchy.
    • 'zzzz ServProvDocs' is the subfolder where 'Premises Staff Schedules' is located.
    • 'Premises Staff Schedules' is the subfolder where 'TasksTodayAA.xlsm' is located.
    • '/TasksTodayAA.xlsm' specifies the file name.

    Make sure to adjust the relative path if the folder structure is different or if the workbooks are located in different parent folders.

  5. User

    My workbook 'Service Providers.xlsm' is in a folder named 'SERVICE PROVIDERS' The code below is in a sheet in my workbook.

    The following code in my sheet works and the code below activates the workbook 'TasksTodayAA.xlsm'. If I change tthe address to a relative path, I get an error indicating that the path is not found. How can I stop this error. I have hundreds of addresses I need to change.

    Sub OpenPremAssHalfDaySchedule() Dim waasch As Workbook Set waasch = Workbooks.Open("J:\Facilities Management\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs\Premises Staff Schedules\TasksTodayAA.xlsm") waasch.Activate End Sub

  6. Assistant

    To avoid the error when using a relative path, you need to ensure that your relative path is correct and relative to the location of your current workbook.

    The issue you might be facing is that the relative path you're using is not referencing the correct location of the 'TasksTodayAA.xlsm' workbook.

    Since your current workbook, 'Service Providers.xlsm', is in the 'SERVICE PROVIDERS' folder and the 'TasksTodayAA.xlsm' workbook is in the 'Premises Staff Schedules' subfolder, you can use the following relative path:

    Sub OpenPremAssHalfDaySchedule() Dim folderPath As String Dim waasch As Workbook

    folderPath = ThisWorkbook.Path
    Set waasch = Workbooks.Open(folderPath & "\zzzz ServProvDocs\Premises Staff Schedules\TasksTodayAA.xlsm")
    
    waasch.Activate
    

    End Sub

    In this code, 'ThisWorkbook.Path' returns the path of the current workbook ('Service Providers.xlsm'). Then, by appending the relative path of the 'TasksTodayAA.xlsm' workbook to 'folderPath', you can open the workbook without any errors.

    Make sure that the folder structure is exactly as mentioned in the question, and that both workbooks are saved in the correct folder locations.

  7. User

    Is there a simpler way of doing this, I have hundreds of addresses I need to change

  8. Assistant

    Yes, there is a simpler way to handle multiple addresses without modifying each one individually. You can create a function that takes the folder structure and file name as inputs, and it will automatically generate the correct relative path for opening the workbook.

    Here's an example of how you can implement this:

    1. Create a new module in your workbook by going to the VBA editor (Alt + F11) and inserting a new module.

    2. In the new module, paste the following code:

    Function GetRelativePath(folderStructure As String, fileName As String) As String
        Dim parentFolder As String
        Dim relativePath As String
        
        parentFolder = ThisWorkbook.Path
        relativePath = parentFolder & "\" & folderStructure & "\" & fileName
        
        GetRelativePath = relativePath
    End Function
    
    1. Save the module and return to your worksheet.

    2. Now you can use the GetRelativePath function to generate the relative path for opening the workbook. Here's an updated version of your original code to demonstrate this:

    Sub OpenPremAssHalfDaySchedule()
        Dim waasch As Workbook
        Dim relativePath As String
        
        relativePath = GetRelativePath("zzzz ServProvDocs\Premises Staff Schedules", "TasksTodayAA.xlsm")
        Set waasch = Workbooks.Open(relativePath)
        
        waasch.Activate
    End Sub
    

    In this code, the GetRelativePath function takes the folder structure ("zzzz ServProvDocs\Premises Staff Schedules") and the file name ("TasksTodayAA.xlsm") as arguments. It combines the parent folder path of the current workbook with the folder structure and file name to generate the correct relative path.

    This way, you can simply update the relative folder structure and file name in the OpenPremAssHalfDaySchedule subroutine without changing the path for each address individually.