reference another workbook in VBA

seeker123

Board Regular
Joined
Oct 8, 2011
Messages
84
Hi everyone

I have several excel files open and I have a workbook named "Master" ,that workbook has a sheet called "Data Base", I want to activate the workbook that its name is in the cell(1,3) of "Data Base" worksheet I had wrote the code below

Workbooks(Workbooks("Master").Worksheets("Data Base").Cells(1, 3).Text + ".xlsm").Activate


but I get "Subscript out of range" error.
I know this is a reference error because when I type the workbook name it works.
Any ideas how I can solve this?

thanks in advance
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
15,023
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
The code works fine for me. Are you 100% certain that the filename in C1 is 100% correct (including the spaces in the name and any leading or trailing spaces) and the workbook is open.

Does the code below give you exactly what you expect?

Code:
Sub xxx()
MsgBox Workbooks(Workbooks("Master").Worksheets("Data Base").Cells(1, 3).Text + ".xlsm").FullName
End Sub
 
Upvote 0

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
15,023
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Actually open Workbook master and put both the codes below in the same module and make sure that your immediate window is open.
Run the first code.
Open the other workbook and run the second code.

Do they exactly match

Code:
Sub xxx1()
Dim i As String
i = Workbooks("Master").Worksheets("Data Base").Cells(1, 3).Text + ".xlsm"
Debug.Print i
End Sub
Code:
Sub xxx2()
Dim x As String
x = ActiveWorkbook.Name
Debug.Print x
End Sub
 
Upvote 0

seeker123

Board Regular
Joined
Oct 8, 2011
Messages
84
Mark Thanks for your reply.

I just changed the "Master" to "Master.Xlsm" and it worked.!!
Thanks alot.
 
Upvote 0

Forum statistics

Threads
1,191,034
Messages
5,984,261
Members
439,881
Latest member
Amitoj95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top