VBA通过部分名称识别工作簿

Question

有没有办法在没有全名的情况下为工作表指定工作簿？例如，如果工作簿名称是MyWorbook2015，并且2015将来可能会更改为2016，那么我需要根据MyWorkbook识别工作簿，并忽略2015。类似这样的:

With Workbooks("MyWorkbook2015").Sheets("My_Sheet_Name_That_Does_Not_Change")
     'do something
End With

在上面的代码示例中，我需要它来识别工作簿，无论日期如何？这可能吗？如果是的话，我该怎么做？

Answer 1

是的，您可以使用带有通配符“*”的LIKE运算符。这是一个例子。我假设工作簿已打开。

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

编辑

这是一种将其用作函数的方法

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