SOLVED

Specific sub to open a workbook (hidden) so multiple subs can access the worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1668492%22%20slang%3D%22en-US%22%3ESpecific%20sub%20to%20open%20a%20workbook%20(hidden)%20so%20multiple%20subs%20can%20access%20the%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1668492%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20userform%20that%20accesses%20data%20from%20an%20external%20workbook%20(at%20different%20times).%26nbsp%3B%20I%20currently%20am%20opening%20this%20workbook%20and%20subsequent%20worksheets%20in%20each%20sub.%26nbsp%3B%20In%20an%20effort%20to%20clean%20up%20duplicate%20coding%2C%20is%20there%20a%20way%20that%20I%20can%20create%20a%20sub%20that%20will%20open%20the%20workbook%20and%20in%20each%20required%20sub%20routine%2C%20access%20that%20workbook%20to%20extract%20data%20from%20it%20(Note%3A%20no%20writing%20is%20done%20to%20this%20workbook%2C%20only%20data%20extraction).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1668492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1673234%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20sub%20to%20open%20a%20workbook%20(hidden)%20so%20multiple%20subs%20can%20access%20the%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1673234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F794223%22%20target%3D%22_blank%22%3E%40asmenut%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CBR%20%2F%3EIf%20I%20understand%20your%20need%2C%20I%20would%20create%20a%20function%20to%20get%20the%20workbook%20for%20each%20routine.%20The%20function%20would%20only%20open%20the%20workbook%20if%20it%20isn't%20already%20open.%26nbsp%3B%20So%20if%20a%20subroutine%20needs%20the%20workbook%20it%20would%20call%20GetWkb().%20GetWkb()%20would%20see%20if%20the%20workbook%20is%20open%2C%20and%20if%20so%2C%20return%20it%20(quick!).%20If%20it%20isn't%20open%2C%20it%20would%20open%20it%20and%20return%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20of%20one%20of%20the%20calling%20routines%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20Sub1()%3CBR%20%2F%3E%20%20%20%20Dim%20oWkb%20As%20Workbook%3CBR%20%2F%3E%20%20%20%20Set%20oWkb%20%3D%20GetWkb%3CBR%20%2F%3E%20%20%20%20'Do%20something%20with%20the%20workbook%20here%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3EThe%20function%20would%20be%3A%3C%2FP%3E%3CPRE%3EPublic%20Function%20GetWkb()%20As%20Workbook%3CBR%20%2F%3E%3CBR%20%2F%3E%20%20%20%20Static%20oWkb%20As%20Workbook%20%20%20%20%20%20%20%20%20%20%20%20%20%20'Static%20to%20keep%20it%20in%20memory%20when%20this%20function%20ends%3CBR%20%2F%3E%20%20%20%20Const%20sWkb%20As%20String%20%3D%20%22MyData.xlsx%22%20'Change%20this%20to%20your%20master%20workbook's%20name%3CBR%20%2F%3E%20%20%20%20Const%20sPath%20As%20String%20%3D%20%22C%3A%5CTemp%5C%22%20%20%20'Change%20this%20to%20your%20master%20workbook's%20path%3CBR%20%2F%3E%3CBR%20%2F%3E%20%20%20%20On%20Error%20GoTo%20ErrHandler%3CBR%20%2F%3E%3CBR%20%2F%3E%20%20%20%20If%20Not%20oWkb%20Is%20Nothing%20Then%3CBR%20%2F%3E%20%20%20%20'%20%20%20%20Make%20sure%20it%20hasn't%20been%20closed%20by%20some%20other%20process%3CBR%20%2F%3E%20%20%20%20%20%20%20%20If%20oWkb.Name%20%3D%20vbNullString%20Then%20Set%20oWkb%20%3D%20Nothing%3CBR%20%2F%3E%20%20%20%20End%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%20%20%20%20If%20oWkb%20Is%20Nothing%20Then%3CBR%20%2F%3E%20%20%20%20'%20%20%20See%20if%20it%20was%20opened%20by%20some%20other%20process%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Set%20oWkb%20%3D%20Application.Workbooks(sWkb)%3CBR%20%2F%3E%20%20%20%20'%20%20%20If%20still%20nothing%2C%20open%20the%20workbook%20%3CBR%20%2F%3E%20%20%20%20%20%20%20%20If%20oWkb%20Is%20Nothing%20Then%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20Set%20oWkb%20%3D%20Application.Workbooks.Open(Filename%3A%3DsPath%20%26amp%3B%20sWkb%2C%20ReadOnly%3A%3DTrue)%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20ActiveWindow.Visible%20%3D%20False%3CBR%20%2F%3E%20%20%20%20%20%20%20%20End%20If%3CBR%20%2F%3E%20%20%20%20End%20If%3CBR%20%2F%3E%20%20%20%20Set%20GetWkb%20%3D%20oWkb%3CBR%20%2F%3E%3CBR%20%2F%3EErrHandler%3A%3CBR%20%2F%3E%20%20%20%20Select%20Case%20Err.Number%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Case%20Is%20%3D%200%3A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'No%20Error%2C%20Do%20nothing%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Case%20Is%20%3D%209%3A%20Resume%20Next%20%20%20%20%20%20%20%20%20%20%20'Workbook%20not%20open%20error%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Case%20Is%20%3D%20-2147221080%3A%20Resume%20Next%20'Workbook%20closed%20error%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Case%20Else%3A%3CBR%20%2F%3E%20%20%20%20%20%20%20%20'%20%20%20Some%20unanticipated%20error%20happened%20so%20report%20it%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20Prompt%3A%3D%22Error%23%22%20%26amp%3B%20Err.Number%20%26amp%3B%20vbLf%20%26amp%3B%20Err.Description%2C%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20Buttons%3A%3DvbCritical%20%2B%20vbMsgBoxHelpButton%2C%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20Title%3A%3D%22GetWkb%22%2C%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20HelpFile%3A%3DErr.HelpFile%2C%20_%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20Context%3A%3DErr.HelpContext%3CBR%20%2F%3E%20%20%20%20End%20Select%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1674378%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20sub%20to%20open%20a%20workbook%20(hidden)%20so%20multiple%20subs%20can%20access%20the%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F234290%22%20target%3D%22_blank%22%3E%40Craig%20Hatmaker%3C%2FA%3E%26nbsp%3B%2C%20works%20wonderfully.%26nbsp%3B%20Thank%20you%20very%20much.%26nbsp%3B%20The%20only%20addition%20I%20have%20to%20make%20to%20the%20specific%20sub%20routines%20is%20remembering%20to%20close%20the%20workbook%20and%20set%20to%20nothing%20(or%20else%20the%20grand%20automation%20error).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a userform that accesses data from an external workbook (at different times).  I currently am opening this workbook and subsequent worksheets in each sub.  In an effort to clean up duplicate coding, is there a way that I can create a sub that will open the workbook and in each required sub routine, access that workbook to extract data from it (Note: no writing is done to this workbook, only data extraction).

 

3 Replies
Highlighted
Best Response confirmed by asmenut (New Contributor)
Solution

@asmenut , 
If I understand your need, I would create a function to get the workbook for each routine. The function would only open the workbook if it isn't already open.  So if a subroutine needs the workbook it would call GetWkb(). GetWkb() would see if the workbook is open, and if so, return it (quick!). If it isn't open, it would open it and return it. 

 

Here is an example of one of the calling routines

 

Sub Sub1()
Dim oWkb As Workbook
Set oWkb = GetWkb
'Do something with the workbook here
End Sub

The function would be:

Public Function GetWkb() As Workbook

Static oWkb As Workbook 'Static to keep it in memory when this function ends
Const sWkb As String = "MyData.xlsx" 'Change this to your master workbook's name
Const sPath As String = "C:\Temp\" 'Change this to your master workbook's path

On Error GoTo ErrHandler

If Not oWkb Is Nothing Then
' Make sure it hasn't been closed by some other process
If oWkb.Name = vbNullString Then Set oWkb = Nothing
End If

If oWkb Is Nothing Then
' See if it was opened by some other process
Set oWkb = Application.Workbooks(sWkb)
' If still nothing, open the workbook
If oWkb Is Nothing Then
Set oWkb = Application.Workbooks.Open(Filename:=sPath & sWkb, ReadOnly:=True)
ActiveWindow.Visible = False
End If
End If
Set GetWkb = oWkb

ErrHandler:
Select Case Err.Number
Case Is = 0: 'No Error, Do nothing
Case Is = 9: Resume Next 'Workbook not open error
Case Is = -2147221080: Resume Next 'Workbook closed error
Case Else:
' Some unanticipated error happened so report it
MsgBox _
Prompt:="Error#" & Err.Number & vbLf & Err.Description, _
Buttons:=vbCritical + vbMsgBoxHelpButton, _
Title:="GetWkb", _
HelpFile:=Err.HelpFile, _
Context:=Err.HelpContext
End Select

End Function

Hope that helps

Highlighted

@Craig Hatmaker , works wonderfully.  Thank you very much.  The only addition I have to make to the specific sub routines is remembering to close the workbook and set to nothing (or else the grand automation error).

 

 

Highlighted

@asmenut , 

I recommend putting a routine in the Workbook_BeforeClose event to close the master workbook. I recommend the individual routines not attempt to close the master workbook. That would only slow things down.