windows.visible = false preventing use of named range

Question

I have a set of userforms which write to a database (for ease of data input). Two of the userforms utilize a named range from the database for combobox rowsource:

Application.Workbooks.Open "path\TestDestination.xlsx"
 Me.ContactCompany.RowSource = "test.CompanyList"

This works fine, but opens the database file, even if I turn screen updating off (?). Upon further searching, it seemed that application.screenupdating = false would be exactly what I was looking for. I changed those lines to the code below:

 Application.Workbooks.Open "path\TestDestination.xlsx"
 Me.ContactCompany.RowSource = "test.CompanyList"   
 Windows("TestDestination.xlsx").Visible = False'(Also tried activewindow.visible = false)

This does work to hide the workbook, but for whatever reason renders the named range (test.CompanyList) unusable by the input workbook and returns an out of range error. The only solution I was able to find through extensive searching suggested that maybe it was due to it adding [hidden] to the filename, therefore creating a miscommunication (if I understood correctly). To try to circumvent this I inserted this line immediately after it is opened

ActiveWindow.Caption = TestDestination 

No dice. I apologize if this is an obvious solution or if I am not explaining well, I do not know VBA at all I am just learning as I go for a work assignment. Any advice would be greatly appreciated. Thank you.

Answer 1

When you say database I take it you mean another Excel file and not something like Access or MySQL.

Referring to the named range this way works
wrkBk.Names("MyNamedRange").RefersToRange
In the example I've placed numbers within the named range and display a message box giving the sum of the numbers while the source workbook is hidden.

Sub Test()

    Dim wrkBk As Workbook

    Set wrkBk = Workbooks.Open("path\TestDestination.xlsx")
    Windows(wrkBk.Name).Visible = False
    MsgBox WorksheetFunction.Sum(wrkBk.Names("MyNamedRange").RefersToRange)
    Windows(wrkBk.Name).Visible = True

End Sub