Using variables between open workbooks?

[waynep]

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

 

[Andrew Poulsom]

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:

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]

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]

If you want to run a macro in the workbook just opened:

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

Is that what you mean?

 

[waynep]

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]

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]

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:

Public wkbEst As Workbook

Then I have this code in the template file to call a database sheet (wkbMat is the database sheet):

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:

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]

My fault - the scope of a Public variable is limited to the Project in which it is declared.

This worked for me:

' 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]

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. )]

Thanks again,
Wayne

 

[waynep]

Woo-hoo!

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

Have a great weekend,
Wayne