Opening an excel file using a wildcard in VBA

Question

This one is giving me quite a headache as none of the solutions I found online work for me.

I have a folder with a number of excel files which are always named with an alpha-2 country code and a random date (e.g. GB-YYYY-M-D.xlsx). I need to pass the path and the country code as variables to function which opens the file. The dates in filename are not relevant to this and are kind of random, thus I need to replace them with a wildcard. With the example earlier I imagine something like 'path & "\" & countrycode & *.xlsx'. I have learned that this will not work directly with 'Workbooks.open' as it would pass the asterix into the filename instead of it being a wildcard.

Essentially, I need to open a file based on the path and country code provided by variables, perform some actions in it, save and close. Then I need to do the same for the next file and so on. This part I have figured out, however I am unable to get the workbooks open.

A lot of the solutions online discuss the Dir() function in combination with a loop however I don't seem to be able to get it to work as it always returns an empty string. I'm unclear on how to approach this and as you can see I am no VBA expert :D. Any help would be much appreciated.

Dir() is the way to go here Dim f: f = Dir(folderPath & countrycode & "-*.xlsx") assuming folderpath includes the terminating \ If you're having problems getting that to work, please update your post with the actual code you're trying. — Tim Williams
– Tim Williams, Commented Jul 20, 2022 at 23:18

ALeXceL · Accepted Answer · 2022-07-21 03:31:48Z

0

Check this code, running the sub 'fnTest'. As a matter of exercise I put the parameters to return a valid file name. If there is only one file that correspond to, say, "GB*.*, this code will return it correctly.

Option Explicit

Sub fnTest()
    Dim strFileToOpen As String

    strFileToOpen = fnDirSomeFile("C:\Windows\System32\", "GP")
    If strFileToOpen <> "" Then
        MsgBox strFileToOpen
        'here is what goes on
        'Workbooks.Open strFileToOpen
    End If
    
End Sub

Function fnDirSomeFile(ByVal strPath As String, ByVal strCountry As String) As String
    Dim strFile As String
    Dim strCheckFile As String

    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    
    strFile = Dir(strPath & strCountry & "*.*", vbNormal)
    fnDirSomeFile = strFile
    
lblExit:
    
End Function

answered Jul 21, 2022 at 3:31

ALeXceL

6597 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iHaveTinyHands Over a year ago

Thank you, your solution works! Albeit not as you might have expected: initially it did not work for me, 'strFile = Dir()' was returning an empty string but it did lead me to an AHAAAA moment: Dir() does not seem to work with OneDrive, which is where our company files are stored. I moved the folder to C: and now it runs smoothly. Since having it on OneDrive is a necessity in this case, I will be using a workaround- first copy from OneDrive to local and after making adjustments I will move the entire folder.

Collectives™ on Stack Overflow

Opening an excel file using a wildcard in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related