Reading a Global Variable from another workbook

[450nick]

Hi all,

I'm currently using a workbook to open several other workbooks and run macros within them. Each macro that runs populates a Global variable within its self to say whether or not each macro ran successfully. Once they are finished, before I close the workbook, I would like the parent workbook to read these global variables to report back on whether the tasks were completed successfully. Anyone know the correct code to do this?

 

[Jaafar Tribak]

Try defining the Global variables as Public in the ThisWorkbook module of each workbook so you can access the variables from you parent workbook as follows :

Wb.GlobalVar ( where Wb is the pointer to the opened workbook that you get when the workbook is opened)

 

[450nick]

Hi Jaafar, I actually ended up doing using this method: Hidden Name Space - a really neat solution it seems. I will have a look at your method too though as I searched all over and couldn't find any mention of using GlobalVar - looks useful!

 

[Jaafar Tribak]

Hi 450nick,

GlobalVar was just a random name I chose for your Global variable .. You should replace it with the actual name of your global variable

 

[Jaafar Tribak]

There are many simpler methods than using the Application Hidden Name Space to store data .. If you don't want to use a Public variable you could temporarly store the data somewhere in the opened workbook .. For example, a good place for data storage is the Comments Property of the Workbook Object .

Here is an example:

This is the Macro in the opened workbook

Sub Macro()

[COLOR=#008000]    'Body of your Macro[/COLOR]
[COLOR=#008000]    '======================[/COLOR]
    'Run your code here ....
    
    
[COLOR=#008000]    'If Macro completed successfully then edit the Wbk Comments Property at the end of the Macro.[/COLOR]
[COLOR=#008000]    '========================================================[/COLOR]
    ThisWorkbook.Comments = "Task was completed successfully."
End Sub

And this is the code in the Parent workbook : (Change the path and name of the target workbook as required)

Sub Test()
    Dim oWb As Workbook
    Dim sComment As String


    Application.ScreenUpdating = False
[COLOR=#008000]    'Open workbook and save a pointer to it.[/COLOR]
    Set oWb = Workbooks.Open("c:\Test\TargetWorkbook.xls")
[COLOR=#008000]    'Store initial value of the workbook comments Property.[/COLOR]
    sComment = oWb.Comments
[COLOR=#008000]    'Run the Macro in the just opened workbook.[/COLOR]
    Application.Run oWb.Name & "!Macro"
[COLOR=#008000]    'Read the Data (report back if task completed successfully)[/COLOR]
    MsgBox oWb.Comments
[COLOR=#008000]    'Restore initial value of the workbook comments Property.[/COLOR]
    oWb.Comments = sComment
[COLOR=#008000]    'Close the workbook.[/COLOR]
    oWb.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub

 

[450nick]

Good knowledge - thanks very much for the tips!

 

[GTO]

Greetings Nick,

Here would be an example which hopefully accurately portrays using a public variable as Jaafar mentioned, as well as changing the called subs to functions. Let us say we have two workbooks in the same folder; Book1 and Book2 - Book2 being the called workbook.

In Book2.xls...

In ThisWorkbook Module:

Option Explicit

Public bolRanOkay As Boolean

In a Standard Module:

Option Explicit

Public Sub RunMe()
  ThisWorkbook.bolRanOkay = True
End Sub

Public Function RunMeFunction() As Boolean
  RunMeFunction = True
End Function

In Book1.xls (the calling workbook) in a Standard Module:

Option Explicit

Sub exampleCallFromBook1ToBook2()
Dim wb As Workbook
  
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls", , True)
  'Run the Sub and...
  Application.Run wb.Name & "!" & "Module1.RunMe"
  'return the Public variabble housed in ThisWorkbook
  MsgBox wb.bolRanOkay
  
  'Or, change the Sub(s) to Function(s) and use the function's return
  MsgBox Application.Run(wb.Name & "!" & "Module1.RunMeFunction")
  ' (just to show using single quotes if there are any space(s) in wb.Name
  MsgBox TypeName(Application.Run("'" & wb.Name & "'!" & "Module1.RunMeFunction"))
  
  wb.Close False
  
End Sub

Hope that helps,

Mark

 

[450nick]

Awesome, thanks Mark - looks nice and neat, just what I was after...

 

[GTO]

Thanks to Jaafar; all I did was provide an example of the methodology. Glad it works and that we were able to help