How to pass workbook name between subroutines

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:

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

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

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

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!