打开文件名中包含特定字符串的工作簿

Question

一个名为Folder_1的文件夹中有13个文件。这13个文件的名称如下：

1. 2020-01_Template_XYZ.xlsm
2. 2020-02_Template_XYZ.xlsm
3. 2020-03_Template_XYZ.xlsm
4. 2020-04_Template_XYZ.xlsm
5. 2020-05_Template_XYZ.xlsm
6. 2020-06_Template_XYZ.xlsm
7. 2020-07_Template_XYZ.xlsm
8. 2020-08_Template_XYZ.xlsm
9. 2020-09_Template_XYZ.xlsm
10. 2020-10_Template_XYZ.xlsm
11. 2020-11_Template_XYZ.xlsm
12. 2020-12_Template_XYZ.xlsm
13. Master_file.xlsm

Master_file.xlsm包含一个如下所示的宏：

Option Explicit
Sub OpenWorksheets()
Dim workbook_1 As Workbook
Dim workbook_2 As Workbook
Dim workbook_3 As Workbook
Dim workbook_4 As Workbook
Dim workbook_5 As Workbook
Dim workbook_6 As Workbook
Dim workbook_7 As Workbook
Dim workbook_8 As Workbook
Dim workbook_9 As Workbook
Dim workbook_10 As Workbook
Dim workbook_11 As Workbook
Dim workbook_12 As Workbook
Dim active_workbook As Workbook
Set workbook_1 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-01_Template_XYZ.xlsm")
Set workbook_2 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-02_Template_XYZ.xlsm")
Set workbook_3 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-03_Template_XYZ.xlsm")
Set workbook_4 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-04_Template_XYZ.xlsm")
Set workbook_5 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-05_Template_XYZ.xlsm")
Set workbook_6 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-06_Template_XYZ.xlsm")
Set workbook_7 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-07_Template_XYZ.xlsm")
Set workbook_8 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-08_Template_XYZ.xlsm")
Set workbook_9 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-09_Template_XYZ.xlsm")
Set workbook_10 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-10_Template_XYZ.xlsm")
Set workbook_11 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-11_Template_XYZ.xlsm")
Set workbook_12 = Workbooks.Open(Application.ActiveWorkbook.Path & "2020-12_Template_XYZ.xlsm")
Set active_workbook = Workbooks.Open(Application.ActiveWorkbook.FullName)
End Sub

Folder_2还包含13个文件。这13个文件的名称如下：

1. 2021-01_Template_ABC.xlsm
2. 2021-02_Template_ABC.xlsm
3. 2021-03_Template_ABC.xlsm
4. 2021-04_Template_ABC.xlsm
5. 2021-05_Template_ABC.xlsm
6. 2021-06_Template_ABC.xlsm
7. 2021-07_Template_ABC.xlsm
8. 2021-08_Template_ABC.xlsm
9. 2021-09_Template_ABC.xlsm
10. 2021-10_Template_ABC.xlsm
11. 2021-11_Template_ABC.xlsm
12. 2021-12_Template_ABC.xlsm
13. Master_file.xlsm

如果Master_file.xlsm中的宏代码没有更改，我将不得不通过编写如下代码来相应地调整宏：

Option Explicit
Sub OpenWorksheets()
Dim workbook_1 As Workbook
Dim workbook_2 As Workbook
Dim workbook_3 As Workbook
Dim workbook_4 As Workbook
Dim workbook_5 As Workbook
Dim workbook_6 As Workbook
Dim workbook_7 As Workbook
Dim workbook_8 As Workbook
Dim workbook_9 As Workbook
Dim workbook_10 As Workbook
Dim workbook_11 As Workbook
Dim workbook_12 As Workbook
Dim active_workbook As Workbook
Set workbook_1 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-01_Template_ABC.xlsm")
Set workbook_2 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-02_Template_ABC.xlsm")
Set workbook_3 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-03_Template_ABC.xlsm")
Set workbook_4 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-04_Template_ABC.xlsm")
Set workbook_5 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-05_Template_ABC.xlsm")
Set workbook_6 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-06_Template_ABC.xlsm")
Set workbook_7 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-07_Template_ABC.xlsm")
Set workbook_8 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-08_Template_ABC.xlsm")
Set workbook_9 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-09_Template_ABC.xlsm")
Set workbook_10 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-10_Template_ABC.xlsm")
Set workbook_11 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-11_Template_ABC.xlsm")
Set workbook_12 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-12_Template_ABC.xlsm")
Set active_workbook = Workbooks.Open(Application.ActiveWorkbook.FullName)
End Sub

与其这样做，我希望Master_file.xlsm的宏代码能够在其文件夹中查找文件名包含字符串-01_Template_、-02_Template_、…、-12_Template_的文件，并将它们分配给workbook_1、workbook_2、…和workbook_12。

写Set workbook_1 = Workbooks.Open(Application.ActiveWorkbook.Path & "*-01_Template_*.xlsm")这样的东西是行不通的，我已经检查了无数关于更改文件名问题的其他问题。

那么，我该如何解决我刚才描述的问题呢？

Answer 1

您可以使用Dir.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function

否则，请考虑将路径的一部分构造为字符串变量，并附加更改的部分，然后将该变量传递到打开命令中。

例如，您可以输入：

Dim sPathLeader$
sPathLeader = Application.ActiveWorkbook.Path & "2021-"

然后，在下面的行中：

Set workbook_1 = Workbooks.Open(Application.ActiveWorkbook.Path & "2021-01_Template_ABC.xlsm")

您可以将其替换为：

Set workbook_1 = Workbooks.Open(sPathLeader & "01_Template_ABC.xlsm")

然后，如果你有很多年的时间，你可以把整个事情放在For循环中，每次迭代都从用不同的年份重建sPathLader开始。

但是，这不是最佳的，尽管在单个用例中，如果情况紧急，它可能会有所帮助。

最好使用Dir来动态确定该路径中的文件名。

否则，您将需要进行大量的验证检查和错误捕获，以设置在找不到文件名时如何反应等。

Answer 2

以下代码可用于获取目录中的文件名列表。然后，您可以一次打开一个文件。

Public Function Test()
Dim dirPath As String: dirPath = "C:Temp"     ' must end in 
Dim coll As Collection
Set coll = GetFilesInDirectory(dirPath, "*.*")
Dim fileName As Variant
For Each fileName In coll
Debug.Print dirPath & fileName
Next fileName

End Function
Public Function GetFilesInDirectory(dirPath As String, Optional criteria As String = "*.*") As Collection

' Send in a directory path and criteria for file names (*.*) ; directory must end in 
' Returns all files names found that are like criteria ; does not return directories
Dim coll As New Collection
Dim fileName As String
fileName = dir(dirPath & criteria)
Do While fileName <> ""
coll.Add fileName
fileName = dir
Loop
Set GetFilesInDirectory = coll

End Function

Answer 3

由于有太多单独的工作簿对象要引用，因此可以执行以下操作：

Sub test()
Dim workbook1 As Workbook, workbook2 As Workbook, active_workbook As Workbook
Set active_workbook = ThisWorkbook '? not sure why this wouldn't be okay in your case     since this runs from master book
For i = 1 To 12:
Select Case i:
Case i = 1:
Set workbook1 = Workbooks.Open(ThisWorkbook.Path & "2020-" & Format(i, "00") & "_Template_ABC.xlsm")
Case i = 2:
Set workbook2 = Workbooks.Open(ThisWorkbook.Path & "2020-" & Format(i, "00") & "_Template_ABC.xlsm")
'you get the pattern i'm sure
End Select
Next i
end sub

不过，可能有更好的方法来组织任何依赖的代码，比如循环并一次处理每个工作簿，而不是需要一次移交所有工作簿。

Answer 4

好的，我想我已经找到了答案。解决方案是写这样的东西：

Set workbook_1 = Workbooks.Open(Application.ActiveWorkbook.Path & "" & Dir(Application.ActiveWorkbook.Path & "" & "*-01_Template_*.xlsm"))

而不是像这样的东西：

Set workbook_1 = Workbooks.Open(Application.ActiveWorkbook.Path & "" & Dir(Application.ActiveWorkbook.Path & "*-01_Template_*.xlsm"))

第一个星号*似乎需要一个特定的位置才能被正确解释。