VBA按部分名称识别工作簿 [英] VBA recognizing workbook by partial name

问题描述

是否可以在没有全名的情况下为工作表指定工作簿? 例如，如果工作簿名称为MyWorbook2015，而将来的2015可能会更改为2016，则我需要识别基于MyWorkbook的工作簿，而忽略2015.类似于以下内容:

Is there a way to specify a workbook for a sheet without having the full name? For example, If the workbook name is MyWorbook2015 and the 2015 may change to 2016 in the future, I need to to recognize the workbook based on MyWorkbook, and ignore the 2015. Something similar to this:

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

在上面的代码示例中，无论日期如何，我都需要它来识别工作簿?这可能吗?如果是这样，我将如何去做?

In the code sample above, I need it to recognize the work book regardless of the date? Is this possible? If it is, how would I go about doing that?

推荐答案

是的，您可以将LIKE运算符与通配符"*"一起使用.这是一个例子.我假设工作簿已打开.

Yes you can use the LIKE Operator with a wildcard "*". Here is an example. I am assuming that the workbook 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

编辑

这里是您可以将其用作功能的一种方式

Here is a way where 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

这篇关于VBA按部分名称识别工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！