Hi guys,

I have a question which I'am unable to answer.

I have a workbook in which certain cells (range C15:C21 and range F15:F21) have a formula which gets values from another workbook.
Example off such a formula : =If(C14="";"";HLOOKUP(B15;'X:...\[File.xlsx]QryReport!$1:$23;16;0))

What the formula does is basically search the value in B15 in a file, in a certain location, and return the value in the 16th row.

Each month I get a new file (always the same format). When the user pushes the button a browser opens and the user selects the file he or she wants.
Thus the links in the formula needs to be changed.

For this, I have following code:

    Sub WbThs_ChangeLink_Excel()
    Dim wbTrg As Workbook
    Dim sLinkNew As String
    Dim aLinks As Variant, vLink As Variant

        Application.ScreenUpdating = False
                    
        myfilepath = Application.GetOpenFilename()
        
        sLinkNew = myfilepath   'Change as required

        Set wbTrg = ThisWorkbook   'Change as required

        Rem Set array with all excel links
        aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

        Rem Replace each excel Link
        If Not IsEmpty(aLinks) Then
            For Each vLink In aLinks
                wbTrg.Changelink Name:=vLink, NewName:=sLinkNew, Type:=xlExcelLinks

        Next: End If

        Application.ScreenUpdating = True
            
        End Sub
The code works perfectly but changes the links in the entire workbook. I just need the code to change the links in certain ranges.
Bassicaly, I need it to do the following:
  • User pushes the button
  • Browser opens
  • User selects the first file
  • Formula's are updated in range C15:C21 with the location of the first file (values are returned from the first file in range C15:C21)
  • New browser opens
  • User selects second file
  • Links are updated in range F15:F21 with the location of the second file (values are returned from the second file in range F15:F21)

I already tried multiple adaptations to the code but I can't get it to do what I want.

Any help would be greatly appreciated .