VBA recognizes workbook by partial name
Is there a way to specify a workbook for a sheet without a full name? For example, if the title of the book is MyWorbook2015, and 2015 could change to 2016, I would need to recognize the book based on MyWorkbook and ignore 2015. Something similar to this:
With Workbooks("MyWorkbook2015").Sheets("My_Sheet_Name_That_Does_Not_Change")
'do something
End With
In the above code example, do I need it to recognize the workbook regardless of the date? Is it possible? If so, how would I do it?
source to share
Yes, you can use the LIKE
wildcard operator "*". Here's an example. I assume the book is open.
Sub Sample()
Dim wb As Workbook
Dim wbName As String
'~~> "MyWorkbook2015"
wbName = "MyWorkbook"
For Each wb In Application.Workbooks
If wb.Name Like wbName & "*" Then
Debug.Print wb.Name
With wb.Sheets("My_Sheet_Name_That_Does_Not_Change")
'~~> Do something
End With
End If
Next wb
End Sub
EDIT
Here you can use it as a function
Dim wbName As String
Sub Sample()
'~~> "MyWorkbook2015"
wbName = "MyWorkbook"
If Not GetWB Is Nothing Then
Debug.Print GetWB.Name
With GetWB.Sheets("My_Sheet_Name_That_Does_Not_Change")
'~~> Do something
End With
Else
MsgBox "No workbook with that name found"
End If
End Sub
Function GetWB() As Workbook
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name Like wbName & "*" Then
Set GetWB = wb
Exit Function
End If
Next wb
End Function
source to share
A somewhat more robust alternative to doing a partial match in Workbook.Name is to do an equivalence match in WorkBook.CodeName. The default CodeName for a workbook is ThisWorkbook, but you can change this by selecting the ThisWorkbook code page in the Project Explorer window, and then open the Properties window (Key: F4) and change the Name property for the code page.
Then, following the example that Siddharth showed, but overriding, then the "GetWB" function looks like this:
Function GetWB() As Excel.Workbook
Const wbCodeName As String = "MySecretWorkbookName" ' change to the CodeName you choose
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.CodeName = wbCodeName Then
Set FindWorkbook = wb
Exit For
End If
Next wb
End Function
Note that changing the CodeName allows the new CodeName to be used in places where you would use "ThisWorkbook", but you can still use "ThisWorkbook".
source to share