VBA macro to activate open workbook based on cell value from another open workbook

[sirbudan82]

I'm stuck with a simple code that I can't make it work. I have two open workbooks and I would like to activate the other workbook from the active workbook based on a cell value. The idea is to activate and close the workbook that includes the database keep the one with the output.

I'm using the code below but I'm getting an error "Subscript out of range"

Sub Switch_Workbooks()
Workbooks(ActiveSheet.Range("B20").Value).Activate
End Sub

Another code line that I could use is Windows("Name Workbook").Activate but I can't make it work to get the value of a cell

Thank you a lot

 

[Scott T]

Do you have the fill file name in B20 including the file extension? It should work then. B20=Book2.xlsx works, B20=Book2 = "subscript out of range" error.

 

[Fluff]

@Scott T
FYI. If you have have your system settings set to Hide extensions for known file types both of your examples will work.

 

[Scott T]

@Scott T
FYI. If you have have your system settings set to Hide extensions for known file types both of your examples will work.

Interesting.


To the OP make sure the file name is spelled correctly and there are no extra spaces.

 

[sirbudan82]

@Scott T @Fluff
Thank you for the help but it is still not working. You can see below the content of the cell B20
"C:\Finance Folder\2017\10 - October\Management Reports\MMR_DB @'17 FX Rates.xlsb"
The easiest way would be to be able to use Windows("Name Workbook").Activate but to get the name of the workbook from a cell.

 

[Fluff]

If both workbooks are open, then you don't need the filepath, just the name. Try changing the value in B20 to MMR_DB @'17 FX Rates.xlsb

 

[Scott T]

Try it without the path and just the file name in B20.

 

[sirbudan82]

It worked without the path , many many thanks. I can't believe I didn't see it.

 

[Fluff]

Glad we could help & thanks for the feedback