Using a template to open an Excel workbook

Question

I want to use a wildcard to open a book stored in the same folder as my macro book. The folder contains a file with the name 302113-401yr-r01.xlsm. Here is my code:

Workbooks.Open filename:=ActiveWorkbook.Path & "\302113*.xlsm"

However, he tells me that there is no such file. Any tips?

Answer 1

We cannot open a file using a template - imagine chaos if we could!

You will need to use Dir(ActiveWorkbook.Path & "\302113*.xlsm")to loop through the files that this returns. If there is only one, then just use this function once:

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 Feature : Network Technology

Answer 2

, / . :

Workbooks.Open filename:=ActiveWorkbook.Path & "\302113*"

, :

Workbooks.Open Filename:="X:\business\2014\Easy*"

.

Answer 3

, UNC - .

:

Set xlFile = xlObj.WorkBooks.Open("\\yourServerHere\dataAutomation\*.xlsx")

Answer 4

I am not so experienced with Excel yet, but the following works well for me to use wildcards in file names to open files. In this example, all files should be in the same directory / folder. Yes, this is quite simplistic.

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