VBA: Wildcard not working with existing code

[drynn]

I have an interesting issue that you may be able to shed
some light on. I have a macro in spreadsheet that opens a
csv file that is in the same directory, manipulates it and
saves it to another folder. The csv file file always has
the same name and that name is hard-coded into the macro
with Workbooks.Open Filename:=ThisWorkbook.Path & "\R99DFG
PARIS (YTD).csv"
The csv file is placed there by an automated process over
which I have no control and each time the automated process
uploads a new file, it first removes the previous one
(daily). The macro has worked happily for over one year.
Now, the source has changed the file name to match an
internal requirement such that the name now takes form of
R99DFG xxxx (YTD).csv, where xxxx is always a random hex
number that I do not know in advance.
I therefore changed the line of code in my macro to
Workbooks.Open Filename:=ThisWorkbook.Path &
"\R99DFG*.csv", thinking that it would act as a wildcard
but all I get is a 'file not found' error.
I have tried with "\R99DFG" & "*" & ".csv" as I have seen
it written this way before but to no avail. Are wildcards
not accepted in Excel 2010 macros, or not acceptable with
this function?
I am more interested to find out why this does not work
than getting suggestions for an alternative piece of code
e.g. I know I could get the operator to select the file
manually during the process as an option.

 

[Andrew Poulsom]

Welcome to MrExcel.

You can't use wildcards in the Filename argument of the Open method. Use Dir with a wildcard to assign the file name to a variable and use that variable to open the file.

 

[drynn]

Thanks for the speedy reply.

This is now somewhat intriguing. I did as you suggested, and I also added a msgbox before the Workbooks.Open Dir(FileName) line to check what was being used and could see that FileName was indeed correctly showing the full path with "\R99DFG*.csv" appended as required. But after OKing the msgbox, it then crashed with a could 'not find the file' error. However, the file name it gave in the error message was the full name, without the wildcard. That surely means that the file was found otherwise it could not have known the full (actual) file name because it is not set anywhere else in the code (and Workbooks.Open is the very next line of code).

The file is not open elsewhere, and if I revert to the full hard coded file name as before, it opens as required.

Am I missing something obvious

 

[Andrew Poulsom]

Please post your VBA code that isn't working.

 

[drynn]

Corny I know, but for reasons of security I cannot post the whole code. I will re-write it into a new macro and see if it displays the same error. If it does, I will post the code here and we can continue. Thanks for helping.

(Is it OK to paste the code directly into this widow?)

 

[Andrew Poulsom]

(Is it OK to paste the code directly into this widow?)

Yes, of course.

 

[drynn]

Well, here is the code in its simplest form. Both the macro file and the data file are in the same directory, and it is not the root directory.

The commented line works fine; the Dir line fails.

Sub test()
Dim FileToOpen As String
'Workbooks.Open FileName:=ThisWorkbook.Path & "\R99DFG xxxx (YTD).csv"
FileToOpen = ThisWorkbook.Path & "\R99DFG*.csv"
MsgBox FileToOpen
Workbooks.Open Dir(FileToOpen)
End Sub

The VB error message is Run-time error 1004, and the full (correct) file path is shown in the message text. (I tried pasting a copy of the error pop-up but it does not work here). I do not understand how it can display the file name in the error message when it has not been defined in the code beforehand - surely it must be finding it?

 

[Andrew Poulsom]

You need to assign the result of Dir to a variable first. Try (untested):

Sub Test()
    Dim FileToOpen As String
    Dim FileName As String
    FileToOpen = ThisWorkbook.Path & "\R99DFG*.csv"
    FileName = Dir(FileToOpen)
    If FileName <> "" Then
        Workbooks.Open ThisWorkbook.Path & "\" & FileName
    End If
End Sub

 

[drynn]

Andrew,

Thanks for your help. I was clearly mis-interpreting the error message and then not using the DIR function correctly. All resloved now, thanks again.