问题描述
我正在编写一个用于工作的快速自动化项目,我无法指定从中打开工作簿的文件路径,因为它是在特殊软件中使用的,无法通过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-如何搜索活动工作簿,并使文件名与字符串匹配?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!