Forum Discussion

philippe_desrochers's avatar
philippe_desrochers
Copper Contributor
Sep 14, 2022

Excel refer to cells in différent  closed worksbooks

Hello,   In workbooks 1.  i have to refer to cells in différent  closed worksbooks in a folder. ( Documents)    I used this formula: ='C:\Documents\[nameofthe files.xls ]Sheet2'! A1   In the sa...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Sep 14, 2022

philippe_desrochers 

If the other workbook is open, you could use the INDIRECT function, but unfortunately, that won't work with a closed workbook.

You might use VBA code to change the formula when you change the value of B3. Would that be acceptable?

  • philippe_desrochers's avatar
    philippe_desrochers
    Copper Contributor
    Sep 15, 2022

    HansVogelaar 

     

    Yes do you have a vba code to suggest? Im not verry good in VBA....

     

    Thank You! 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Sep 15, 2022

      philippe_desrochers 

      Right-click the sheet tab.

      Select 'View Code' from the context menu.

      Copy the code listed below into the worksheet module, and modify it to suit your setup.

      Switch back to Excel and save the workbook as a macro-enabled workbook (*.xlsm).

      Make sure that you allow macros when you open it.

      Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B3"), Target) Is Nothing Then
        On Error Resume Next
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ' A5 is the cell with the formula
        Range("A5").Formula = "='C:\Documents\[" & Range("B3").Value & "]Sheet2'!A1"
        ' You can add lines like the above for other cells if required
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End If
End Sub
      • philippe_desrochers's avatar
        philippe_desrochers
        Copper Contributor
        Sep 21, 2022
        Hi!

        I'm not able to make it work! Here my real data:

        In the current workbooks:
        Name of the closed file to extract data : in cell A7
        Formula in cell D7
        Here the formula:

        = 'https://mapaq-my.sharepoint.com/personal/philippe_desrochers_mapaq_gouv_qc_ca/Documents/Bureau/TEST/[" & Range("A7").Value & "]NOM ENTREPRISE'!A1

        I know the files path is weird, but it's explain by our cloud ..

        the folder name is TEST
        name of the sheet: = NOM ENTREPRISE

        Here the VBA code:

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A7"), Target) Is Nothing Then
        On Error Resume Next
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ' B7 is the cell with the formula
        Range("D7").Formula = "https://mapaq-my.sharepoint.com/personal/philippe_desrochers_mapaq_gouv_qc_ca/Documents/Bureau/TEST/[" & Range("A7").Value & "]NOM ENTREPRISE'!A1"
        ' You can add lines like the above for other cells if required
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        End If
        End Sub


        The VBA code do not seem to run like a macro, because he has no name. When i lauch macro, this vba code do not appear like a macro..

        Thank you for you help!!!

Resources