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
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!!!
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Sep 21, 2022

      philippe_desrochers 

      This is not a macro to be run by the user (you). It is a so-called event procedure that should be run automatically by Excel when you change the value of cell A7.

      Make sure that the code is in the worksheet module, i.e. the module that is activated by right-clicking the sheet tab in Excel and selecting 'View Code' from the context menu.

Resources