How to pass workbook name between subroutines

jvalente · Jun 6th 2016

Hi all,

I have some code that opens a file, if the file name contains X or Y, it does an X defined macro and a Y defined macro.

The issue I'm having, is how to pass the workbook name that I've defined between subroutines.

For instance my code is as follows:

Code

Sub Openfilesatthestart()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sh As Worksheet
Dim sh2 As Worksheet
Dim Sh3 As Worksheet
Dim lastrow As Long
Dim Sheet As Worksheet
Dim PasteStart As Range
Dim filetoopen As Variant
Dim Title As String
Dim filterindex As Integer
Dim finfo As String
' Optimise the sheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
' Set our sheets
Set Sh = ThisWorkbook.Sheets("Daily Volumes Apps")
Set sh2 = ThisWorkbook.Sheets("Daily Volumes Setts")
Set Sh3 = ThisWorkbook.Sheets("Daily Volumes Apprv")
' ** The below code will prompt you to open and a file and then it will copy sheet1 into the Daily Volumes Apps Sheet
' Set up our list of files
finfo = "All Files (*.*),*.*"
' Title
Title = "Please locate and open the latest Volumes Files"
' The below opens our file
Set wb1 = ActiveWorkbook ' this is the workbook we are using Daily Volumes Apps
filetoopen = Application.GetOpenFilename _
(Title:=Title, _
FileFilter:=finfo) ' the window we use to find the file
If filetoopen = False Then
MsgBox "No File Specified.", vbExclamation, "Cancelled"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=filetoopen)
End If
If wb2.Name Like "*Opened*" Then
MsgBox ("Month Opened")
ElseIf wb2.Name Like "*Settled*" Then
MsgBox ("Month Settled")
' clear all the sheets for new data
'sh2.Cells.Clear ' clear the Daily Volumes Apps sheet for new opened data
ElseIf wb2.Name Like "*Approved*" Then
MsgBox ("Month Approved")
ElseIf wb2.Name Like "*Daily Volumes Details*" Then
MsgBox ("Daily")
Else: MsgBox ("Wrong File Selected")
wb2.Close
End 'stop all macros
End If
End Sub

Display More

Instead of the MSGboxs, I want the macro to take data from the file I open and copy and do further manipulation.

The problem is, I don't want to re-open the file again for each new case to get the workbook name (ie wb2 variable). I want to pass that variable name between subroutines. Confused with the syntax so far....

cytop · Jun 6th 2016

Re: How to pass workbook name between subroutines

Just pass it as a normal parameter.

Code

Sub x()
Set wb2 = Workbooks.Open(Filename:=filetoopen)
Select Case True
Case wb2.Name Like "*Opened*"
OpenProc wb2
Case wb2.Name Like "*Settled*"
SettledProc wb2
Case Else
MsgBox ("Wrong File Selected")
wb2.Close
End 'stop all macros
End Select
End Sub
Sub OpenProc(wb2 As Excel.Workbook)
'// Code
End Sub
Sub SettledProc(wb2 As Excel.Workbook)
'// Code
End Sub

Display More

jvalente · Jun 6th 2016

Re: How to pass workbook name between subroutines

Thanks Cytop!

many different ways to skin a cat I suppose. I ended up defining string variables to capture the workbook name and then I passed the string variables in my call procedure.

then in my called procedure, I set the workbook name to the workbook string variable.

interesting that there's always other ways to get to the same solution

cytop · Jun 6th 2016

Re: How to pass workbook name between subroutines

There are 99 ways to do anything in VBA.

Not that it matters for such a simple piece of code, but you already have a reference to the workbook in wb2. You can pass this directly to the procedure; so what's the point of storing the name in a string, passing the string and then using that string to 'find' the workbook in the called procedure? 2 extra steps, must waste all of 6 clock cycles

Seriously though, it's more the principal of the thing - defining the string, storing the name of the workbook in the string, creating another variable to refer to the workbook in the called procedure and setting the reference to the workbook using the string are all unnecessary. Internally, both procedures will refer to the same object so why not simply pass the object to begin with?

jvalente · Jun 6th 2016

Re: How to pass workbook name between subroutines

Quote from cytop;772001

There are 99 ways to do anything in VBA.

Not that it matters for such a simple piece of code, but you already have a reference to the workbook in wb2. You can pass this directly to the procedure; so what's the point of storing the name in a string, passing the string and then using that string to 'find' the workbook in the called procedure? 2 extra steps, must waste all of 6 clock cycles

Seriously though, it's more the principal of the thing - defining the string, storing the name of the workbook in the string, creating another variable to refer to the workbook in the called procedure and setting the reference to the workbook using the string are all unnecessary. Internally, both procedures will refer to the same object so why not simply pass the object to begin with?

Your technique is much more elegant.

The problem is that I'm learning, so I google my problem and I find something that suits my needs and I adjust the code and test.

I googled, how to pass a workbook variable to another sub routine and found someone who used the string solution.

if anyone knows a good way of knowing what every command and syntax does, let me know!

**S O** · Jun 6th 2016

Re: How to pass workbook name between subroutines

Quote

if anyone knows a good way of knowing what every command and syntax does, let me know!

https://msdn.microsoft.com/en-…rary/office/ff194068.aspx

Share