In Excel, I want to refer to a named range in another Workbook

I'm trying to import data from a source workbook and nothing seems to work.

Sub GetData()
    Dim wbSource As Workbook     'Copied from a Microsoft page. These are OK I presume.
    Dim wsSource As Worksheet
    Dim rnSource As Range    
    Dim wsTarget As Worksheet    'on this workbook
    Dim rnTarget As Range

    'I can't set the workbook - neither of these 2 lines work (with the other REMd out, of course)
    Set wbSource = "D:\aaTemp\RIMERN\RIMERN ORDERS V4.2 2025-06-21 .xlsx"
    Set wbSource = Application.Workbooks("D:\aaTemp\RIMERN\RIMERN ORDERS V4.2 2025-06-21 .xlsx")

    'But what I really want is to get the filename & path from a cell in the current workbook.
    '(The user will right-click the filename & 'Copy as Path' then paste it in Cell C4 as the 'filename will change every time this Sheet is used.) 

    Set wbSource = Range("C4").Text

    'That doesn't work either.   :(
    Set wsSource = wbSource.Worksheets("Financial")
    Set rnSource = wsSource.Range("FinDescription")   'named range on the 'Financial' sheet
    Set wsTarget = Worksheets("Setup")                'in the current workbook
'& a pile of code here
End Sub

How do I make the Set wbSource = Range("C4").Text line work?

Solution

You cannot assign a workbook directly using a string like:

Set wbSource = "your\path\file.xlsx"

You must open the workbook first, then assign it:

Set wbSource = Workbooks.Open("your\path\file.xlsx")

So I think you need something like this:

Sub GetData()

    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim rnSource As Range
    Dim wsTarget As Worksheet
    Dim SourcePath As String
    
    ' Get the workbook path from cell C4
    SourcePath = Range("C4").Value
    
    ' Open the workbook (use Set to assign the opened workbook to wbSource)
    Set wbSource = Workbooks.Open(SourcePath, ReadOnly:=True)

    ' Set the worksheet and range within the opened workbook
    Set wsSource = wbSource.Worksheets("worksheetname")
    Set rnSource = wsSource.Range("rangename") ' Ensure the named range exists

    ' Set the target worksheet in the current workbook
    Set wsTarget = ThisWorkbook.Worksheets("targetsheetname")

    ' Your code to process data goes here

    ' Close the source workbook if necessary
    wbSource.Close SaveChanges:=False

End Sub