Using a wildcard to open an excel workbook

Question

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:

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

However, it tells me that there is no such file. Any advice?

you can't use asterisk in path or file name. You need to open each file separately. Search for 'Dir function' here in SO, there are lots of examples for sure. [HERE](http://msdn.microsoft.com/en-us/library/office/gg278779%28v=office.14%29.aspx) you find some information, too. — Kazimierz Jawor, Oct 22 '13 at 20:26

Andy G · Accepted Answer · 2013-10-23T15:52:16.193

22

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:

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 Function :tech on the net

edited Oct 23 '13 at 15:52

answered Oct 22 '13 at 20:28

Andy G

19,232
5
47
69

3

+ 1 `We cannot open a file using a wildcard - imagine the chaos if we could!` So True :) – Siddharth Rout Oct 22 '13 at 20:32
Ah, I see how opening a file using wildcards would be problematic. Thanks for the responses! – DanW Oct 23 '13 at 12:26
1

Just one comment, unless I'm mistaken, it seems that the DIR Function only returns the filename, not the path and file name. So when I use Workbooks.Open in the above code, I had to put use: ActiveWorkbook.Path & "\" & sFound – DanW Oct 23 '13 at 12:56
Thanks for posting back, I've added this to the answer, in case someone else finds it useful. – Andy G Oct 23 '13 at 15:53
2

I don't understand why the comment `the chaos` seems every other programming language supports wildcards, it's up to the coder to code the logic, not the language. – FreeSoftwareServers Mar 03 '21 at 00:11
If it were made easy to open files, from whatever application, based on wildcard matching (regex), then the simplest user-error (or even coder-error) could cause hundreds, perhaps thousands!, of files to start opening. (We could still implement such a feature if we really wanted to.) – Andy G Mar 03 '21 at 10:46

score 3 · Answer 2 · answered May 22 '14 at 09:26

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 & "\302113*"

For example I am using:

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

and it works.

score 2 · Answer 3 · edited Apr 08 '16 at 06:12

2

You can open files using the wildcard, but only with UNC paths for some reason.

For example :

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

edited Apr 08 '16 at 06:12

akmozo

9,829
3
28
44

answered Apr 08 '16 at 04:29

Jason

21
2

This approach does not work for me, cancel my upvote ( it's locked). – PocketLoan May 18 '16 at 16:35

score 2 · Answer 4 · edited Jan 30 '17 at 20:03

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.

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

Using a wildcard to open an excel workbook

4 Answers4

Linked

Related