Referring to a workbook opened in a different module

[esquiresurf]

Hi
The below module will open an .xlsx workbook within a given path directory.
FYI, I have used Public Constants to give a value to theSource_Template_Folder_Path and Source_Template_Name

Dim Template_name As String
Template_name = Source_Template_Folder_Path & Source_Template_Name &".xlsx"
Workbooks.Open (Template_name), ReadOnly:=False

In another module within the same macro I would like to refer back to thisnewly opened .xlsx workbook, for copying data from or pasting data to.

Question:
How do assign a "name" to this newly opened work so I can refer to itin another module?

I would like to avoid using ActiveWorkbook or similar if possible.

If further information is required let me know.

Thank you

 

[dmt32]

Hi,
Declare an Object variable then assign it to the workbook being Opened using the Set keyword

Dim Template_name As String
    Dim wb As Workbook
    
    Template_name = Source_Template_Folder_Path & Source_Template_Name & ".xlsx"
    


    Set wb = Workbooks.Open(Template_name, ReadOnly:=False)
     
     With wb
     
     'do stuff here
     
'save & close
'(change to False to close without saving)
        .Close True
     
     End With
     


    Set wb = Nothing

you can refer to the variable in your code which can be scoped:

- procedure or module level

or

- workbook level (Public)

or

-you can pass it to other procedures as an argument.

Hope Helpful

Dave

 

[esquiresurf]

Dave thank you for your reply.
This is very useful and it will be something I’ll use.


However, it doesn’t seem to be working when I refer to itin another module, within the same macro.

See Module2 below. As an example I would like to justactivate the newly opened workbook “wb_template” however the error says “ObjectRequired”.


Apologies if I’ve missed something very obvious from youreply.





I’m restating below what I’m trying to do…….








Module1:

Sub Open_Template()


 


Dim Template_name As String


Dim wb_template As Workbook


 


Template_name = Source_Template_Folder_Path &Source_Template_Name & ".xlsx"


 


Set wb_template = Workbooks.Open(Template_name,ReadOnly:=False)


 


 ‘ within thismodule I’m able to refer to the workbook “wb_template” which is useful


 


End Sub

Module2:

SubPopulate_Static_Data()


 

wb_template.Activate


 


‘more codewill follow here


 


End Sub

 

[esquiresurf]

Dave thank you for your reply.
This is very useful and it will be something I’ll use.



However, it doesn’t seem to be working when I refer to itin another module, within the same macro.

See Module2 below. As an example I would like to justactivate the newly opened workbook “wb_template” however the error says “ObjectRequired”.



Apologies if I’ve missed something very obvious from youreply.



I’m restating below what I’m trying to do…….





Module1:

Sub Open_Template()

 
Dim Template_name As String

Dim wb_template As Workbook

 

Template_name = Source_Template_Folder_Path &Source_Template_Name & ".xlsx"

 

Set wb_template = Workbooks.Open(Template_name,ReadOnly:=False)
 
 ‘ within thismodule I’m able to refer to the workbook “wb_template” which is useful

 

End Sub

Module2:

SubPopulate_Static_Data()

 

wb_template.Activate

 
‘more codewill follow here

 

End Sub

 

[dmt32]

Dave thank you for your reply.
This is very useful and it will be something I’ll use.


However, it doesn’t seem to be working when I refer to itin another module, within the same macro.

See Module2 below. As an example I would like to justactivate the newly opened workbook “wb_template” however the error says “ObjectRequired”.


Apologies if I’ve missed something very obvious from youreply.

Hi,

In your second code you need to ensure that your variable is available to it
I did state in my post how you can do this

you can refer to the variable in your code which can be scoped:

- procedure or module level

or

- workbook level (Public)

or

-you can pass it to other procedures as an argument.

you need to determine which is most appropriate for your project

One way if codes are in different modules would be to scope your variable as Public


Module 1

Public wb_template As Workbook

Sub Open_Template()
Dim Template_name As String

Template_name = Source_Template_Folder_Path & Source_Template_Name & ".xlsx"


Set wb_template = Workbooks.Open(Template_name, ReadOnly:=False)


'within thismodule I’m able to refer to the workbook “wb_template” which is useful


End Sub

Module 2

Sub Populate_Static_Data()


wb_template.Activate


'more codewill follow here


End Sub

you can read more about variable scope here:https://support.microsoft.com/en-gb/help/141693/scope-of-variables-in-visual-basic-for-applications


Dave