VBA-如何搜索活动工作簿，并使文件名与字符串匹配? [英] VBA - How to search Active Workbooks, and match a filename against string?

问题描述

我正在编写一个用于工作的快速自动化项目，我无法指定从中打开工作簿的文件路径，因为它是在特殊软件中使用的，无法通过VBA轻松访问.

I am coding up a quick automation project for work, I cannot specify a filepath to open a workbook from as it is used in a special software, not easily accessible with VBA.

如果用户A打开此文件:"ABC_todaysdate" ...我将如何告诉Excel循环浏览活动工作簿(用户A可以打开5-6个工作簿)，请在Activeworkbook文件名中找到字母ABC，然后使用在我的其余功能中?

If User A opens this file: "ABC_todaysdate"... How would I tell Excel to cycle through the active workbooks (User A could have 5-6 workbooks open), find the letter ABC in the Activeworkbook filename, and use that in the rest of my function?

VBA代码:

Sub CopyDemand()
Dim filename As String
Dim Wb As Workbook
Dim ws As Worksheet
Dim Wb2 As Workbook

Set Wb = ThisWorkbook
For Each Wb2 In Application.Workbooks
filename = ActiveWorkbook.FullName
If filename Like "demand" Then
Debug.Print ("Found")
''' Insert function to use WB2 and copy over data, compare workbooks etc.
Next
Wb.Activate

End Sub

推荐答案

您可以迭代每个工作簿，但是我认为这里的关键是使工作簿处于活动状态，以便您可以检索包含路径信息的FullName.

You can iterate each workbook, however I think the key here is to make the workbook active so you can retrieve the FullName with the path information.

Option Explicit

Function getWbName(SearchStr As String) As String
On Error GoTo ErrHand:
    Application.ScreenUpdating = False
    Dim wb As Workbook

    getWbName = vbNullString

    For Each wb In Workbooks
        If InStr(1, wb.Name, SearchStr, vbTextCompare) > 0 Then
            wb.Activate
            getWbName = ActiveWorkbook.FullName
            Exit For
        End If
    Next

    'Return the active window and exit
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
    Exit Function

ErrHand:
    Application.ScreenUpdating = True
    MsgBox (Err.Number & " has occured, with description: " & Err.Description)
End Function

Sub Example()
    Debug.Print getWbName("Book2")
End Sub

编辑

更新了上面的代码以改为返回WorkBook对象.

Updated the code above to return the WorkBook Object instead.

Option Explicit

Function getWorkBookByName(SearchStr As String) As Workbook
    Dim wb As Workbook

    For Each wb In Workbooks
        If InStr(1, wb.Name, SearchStr, vbTextCompare) > 0 Then
            Set getWorkBookByName = wb
            Exit Function
        End If
    Next
End Function

Sub Example()
    Dim myWb As Workbook: Set myWb = getWorkBookByName("Book2")
    If Not myWb Is Nothing Then Debug.Print myWb.FullName
End Sub

这篇关于VBA-如何搜索活动工作簿，并使文件名与字符串匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！