VBA to open workbook using references in current workbook

[gberg]

I want to have a macro in "Workbook A" that opens a different Workbook. I have two named range references in "Workbook A"

Data_Job_Log_Location = C:\Users\gberg\OneDrive\AJ - DC Operations\Backlogs
Data_Job_Log = Job Log Coins All Jobs- 2022.xlsm

The location and file name will change so I want to be able to reference the "Data_Job_Log_Location" and "Data_Job_Log" ranges in the code to open the file

Thanks

 

[davesexcel]

It should end up something like this:

Data_Job_Log_Location = C:\Users\gberg\OneDrive\AJ - DC Operations\Backlogs
Data_Job_Log = Job Log Coins All Jobs- 2022.xlsm
myFile=Data_Job_Log_Location & "\"& Data_Job_Log

 

[gberg]

Wouldn't that set the location and file name without being able to change it without going into the code? I probably did a poor job in the explanation.

The workbook I am working in is called "JPR - Monthly Tracker 2022.xlsm" It has a worksheet named "Data". On this worksheet I have the file name and file path references I want the macro to reference. These file names and paths will change so I want to be able to update this info and have the macro still work based on the new values entered

This code works to open the file I want
Workbooks.Open Filename:="C:\Users\gberg\OneDrive\AJ - DC Operations\Backlogs\Job Log Coins All Jobs- 2022.xlsm"


I tried using an Indirect reference but it gives an error
Workbooks.Open Filename:=INDIRECT("""&Data_Job_Log_Location&" \ "&Data_Job_Log&""")

Thanks,

 

[davesexcel]

myFile=Data_Job_Log_Location & "\"& Data_Job_Log

With your named ranges.

myFile=range("Data_Job_Log_Location").value & "\"& range("Data_Job_Log").value

 

[gberg]

Awesome! That worked, thanks for your help!