vba 使用通配符打开 Excel 工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19527415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a wildcard to open an excel workbook
提问by DanW
I want to use a wildcard to open a workbook stored in the same folder as my macro workbook. In the folder is a file named 302113-401yr-r01.xlsm. Here is my code:
我想使用通配符打开与宏工作簿存储在同一文件夹中的工作簿。文件夹中有一个名为302113-401yr-r01.xlsm. 这是我的代码：
Workbooks.Open filename:=ActiveWorkbook.Path & "2113*.xlsm"
However, it tells me that there is no such file. Any advice?
但是，它告诉我没有这样的文件。有什么建议吗？
回答by Andy G
We cannot open a file using a wildcard - imagine the chaos if we could!
我们无法使用通配符打开文件 - 如果可以的话，想象一下混乱！
You'll need to use Dir(ActiveWorkbook.Path & "\302113*.xlsm")to loop through the files that this returns. If there will only be one then just use this function once:
您需要使用Dir(ActiveWorkbook.Path & "\302113*.xlsm")来循环遍历返回的文件。如果只有一个，那么只需使用此功能一次：
Dim sFound As String
sFound = Dir(ActiveWorkbook.Path & "2113*.xlsm") 'the first one found
If sFound <> "" Then
Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If
Dir Function:tech on the net
目录功能：网络技术
回答by LuTze
From my experience this works if you have the wildcard/asterix as the last symbol in the string and if there is only one file. Try doing:
根据我的经验，如果您将通配符/星号作为字符串中的最后一个符号并且只有一个文件，则此方法有效。尝试做：
Workbooks.Open filename:=ActiveWorkbook.Path & "2113*"
For example I am using:
例如我正在使用：
Workbooks.Open Filename:="X:\business14\Easy*"
and it works.
它有效。
回答by Jason
You can open files using the wildcard, but only with UNC paths for some reason.
您可以使用通配符打开文件，但出于某种原因只能使用 UNC 路径。
For example :
例如 ：
Set xlFile = xlObj.WorkBooks.Open("\yourServerHere\dataAutomation\*.xlsx")
回答by BiffRamshot
I'm not that experienced yet with Excel but the following works well for me for using wildcards in filenames to open files. This example requires all files to be in the same directory/folder. Yes, it is pretty simplistic.
我对 Excel 还不是很有经验，但以下内容对我来说很适合在文件名中使用通配符来打开文件。此示例要求所有文件位于同一目录/文件夹中。是的，它非常简单。
Sub using_wildcards_to_open_files_in_excel_vba()
Dim mypath As String
Dim sFilename As String
'Suppose you have three files in a folder
' Named blank.xlsx,, ex1_939_account.xlsx, and ex1_opt 5.xlsx
'Manually open the blank.xlsx file
'The following code lines will open the second two files before closing the previously opened file.
ActiveWorkbook.Activate
mypath = ActiveWorkbook.Path
'opening xlsx file with name containing "939" and closing current file
mypath = mypath & "\*939*.xlsx"
'MsgBox mypath 'Checking
sFilename = Dir(mypath)
'MsgBox sFilename 'Checking
ActiveWorkbook.Close savechanges:=False
Workbooks.Open Filename:=sFilename
ActiveWorkbook.Activate
mypath = ActiveWorkbook.Path
'opening xlsx file with name ending in "opt 5" and closing current file
mypath = mypath & "\*opt 5.xlsx"
'MsgBox mypath 'Checking
sFilename = Dir(mypath)
'MsgBox sFilename 'Checking
ActiveWorkbook.Close savechanges:=False
Workbooks.Open Filename:=sFilename
End Sub
