我想使用通配符打开与我的宏工作簿存储在同一文件夹中的工作簿。该文件夹中有一个名为302113-401yr-r01.xlsm
的文件。这是我的代码:
Workbooks.Open filename:=ActiveWorkbook.Path & "\302113*.xlsm"
然而,它告诉我找不到这个文件。有什么建议吗?
我们不能使用通配符打开文件 - 想象一下如果我们能打开文件会发生什么混乱!
您需要使用Dir(ActiveWorkbook.Path & "\302113*.xlsm")
循环遍历返回的文件。如果只有一个文件,则只需使用此函数一次:
Dim sFound As String
sFound = Dir(ActiveWorkbook.Path & "\302113*.xlsm") 'the first one found
If sFound <> "" Then
Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If
Dir 函数 :tech on the net
从我的经验来看,如果在字符串中通配符“*”是最后一个符号并且只有一个文件时,这种方法是有效的。尝试执行以下操作:
Workbooks.Open filename:=ActiveWorkbook.Path & "\302113*"
Workbooks.Open Filename:="X:\business\2014\Easy*"
它可以正常工作。
Set xlFile = xlObj.WorkBooks.Open("\\yourServerHere\dataAutomation\*.xlsx")
我对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