Using variables between open workbooks?

waynep

New Member
Joined
Jan 6, 2004
Messages
30
Hello,

Is there a way to use variables between different open workbooks?

I'm writing an application based around a template file and multiple (~15) fixed spreadsheets which collectively act as a database. A user will create a new spreadsheet based on the template, and from this spreadsheet they will randomly open one or more database sheets, one at a time, to select items that get copied back to the original sheet that was based on the template.

Because I'll never know what the user names their file, currently I am referring to the original file with "Workbooks(1).Worksheet("MySheet")"; however this breaks if files are opened in the wrong order. Also, in the database sheets I have a need to call code residing in the template-based file; I've tried Application.Run without success.

I would like to be able to set the user's file name and the current database file name as variables so that I can refer to each from the other. Is it possible to do this?

Thank you,
Wayne
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The workbook in which the code resides can be referred to as ThisWorkbook. When you open a workbook it becomes the ActiveWorkbook (until you activate another). You can use an object variable to keep track of a workbook that is opened in code, eg:

Code:
Dim wb As Workbook
Workbooks.Open "YourWorkbook.xls"
Set wb = ActiveWorkbook

Once set you can use the object variable in place of the workbook's name. You can do the same thing with Worksheets.

Hope that gives you some ideas.
 

waynep

New Member
Joined
Jan 6, 2004
Messages
30
Thanks for responding so quickly!

So after I set the object variable before opening a database sheet, I can refer to the variable from the database sheet? I thought I had tried that but I'll have to go back and look - I'm at my day job and don't have the code in front of me.

Then, is Application.Run the best way to call a main-sheet function from a database sheet?

Thanks again,
Wayne
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you want to run a macro in the workbook just opened:

Code:
Dim wb As Workbook 
Workbooks.Open "YourWorkbook.xls" 
Set wb = ActiveWorkbook 
Application.Run "'" & wb.Name &"'!YourMacro"

Is that what you mean?
 

waynep

New Member
Joined
Jan 6, 2004
Messages
30

ADVERTISEMENT

I don't think so.

From the main sheet, the user opens one of the 15 database sheets. They review the items in that sheet, and enter numbers for how many of each item they want, and then press a button to copy these items back to the main sheet (then the database sheet closes).

The code that copies items back to the main sheet resides within the database sheet. But from the DB sheet I want to call a procedure (residing in the main sheet) that inserts lines within the main sheet before writing the data. (The client uses the main sheet as a printable form with subtotal, tax, freight, and other information at the bottom, so I can't just keep adding items to the end of the list.)

So from the database sheet I need to refer to the main sheet, which will have a random file name.

Wayne
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Declare a Public object variable at the top of a general module in the main workbook and set it to refer to ThisWorkbook either when it is opened or in the code that opens the database workbook. Then you can use the name of that object in the Run statement in the database workbook like I described previously.
 

waynep

New Member
Joined
Jan 6, 2004
Messages
30

ADVERTISEMENT

Hmmm...I'm still getting a 424 "Object required" error.

In the main workbook (the template file) I have this line in the General section of Module1:
Code:
Public wkbEst As Workbook

Then I have this code in the template file to call a database sheet (wkbMat is the database sheet):
Code:
Public Sub ChooseMaterialGroup(MatGrpNum)
On Error Resume Next

Dim wkbMat As Workbook
Dim strMatGrp As String
Dim celMatGrp As String

strMaterialsFolder = "[path to DB sheets]"
MatGrpNum = MatGrpNum + 2
celMatGrp = Worksheets("Hidden").Cells(MatGrpNum, 2).Value
strMatGrp = strMaterialsFolder & celMatGrp

    Set wkbEst = ActiveWorkbook
    Set wkbMat = Workbooks.Open(Filename:=strMatGrp)

    Unload frmMaterialGroups
    
End Sub

Then, in the database file, I added this simple function to check for the main file's filename:
Code:
Public Sub GetEstName()
MsgBox (wkbEst.Name)
End Sub

But when I execute that last function, I get the 424 error. Not sure what I'm doing wrong - probably in the declare, I think?

Wayne
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
My fault - the scope of a Public variable is limited to the Project in which it is declared.

This worked for me:

Code:
' General module in Book1.xls

Sub Test()
    Const strMatGrp As String = "P:\Temp\MrExcel\Variables\Book2.xls"
    Dim wkbEst As Workbook
    Dim wkbMat As Workbook
    Set wkbEst = ThisWorkbook
    Set wkbMat = Workbooks.Open(Filename:=strMatGrp)
    Application.Run "'" & wkbMat.Name & "'!Test1", wkbEst
End Sub

' General module in Book2.xls

Sub Test1(wb As Workbook)
    MsgBox wb.Name
End Sub

That passes the object variable from Book1 to Book2.
 

waynep

New Member
Joined
Jan 6, 2004
Messages
30
OK, I see what you're saying there, but I need to go the other way: I have to call a function residing in wkbEst while I'm in wkbMat. Can I pass wkbEst over to wkbMat when I open wkbMat?

Sorry, I won't be able to try this out until later tonight...


[edit: Wait, I think I get it - Instead of the "Msgbox" code in Book2.xls Module1, I could just create another wkbEst, and set it according to what was passed from Book1. (That was a terrible explanation, but I think I have it. :biggrin: )]

Thanks again,
Wayne
 

waynep

New Member
Joined
Jan 6, 2004
Messages
30
Woo-hoo! (y)

Andrew, I've got it working. Thank you very much!

Have a great weekend,
Wayne
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,694
Members
425,231
Latest member
mramb

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