Open Excel Workbooks with wildcards 1

[crpjaviman]

I am attempting to open a file that the first part of the name will change on a monthly basis. I recorded a macro and this is the code that it generated:

Workbooks.Open FileName:= _
"\\Trunks Data\Feb 02 123.xls"

My question is: is it possible to open a file with a wildcard "\\Trunks Data\*123.xls"? I have tried different combos with no luck. I am trying to keep it as easy as possbile for the next person.

Any suggestions?

Thanks,
Crpjaviman

 

[dsi]

You could do a simple Dir() to get the filename:

Dim sFile As String

sFile = Dir("\\Trunks Data\*123.xls")
If sFile <> &quot;&quot; then
    Workbooks.Open FileName:= sFile
Else
    MsgBox &quot;Can't Find File: \\Trunks Data\*123.xls&quot;
End If

 

[crpjaviman]

Thanks, dsi. The tip was real helpful. I have it working now.

crpjaviman

 

[crpjaviman]

I have a new problem, in the mornings or after I close the Excel session, I try to run the macro in a new session and the file cannot be found.
Does anyone have a suggestions?
I have this code in the macro, but it does not seem to work. I can force it to work by recording a temp macro and copying the path in a certain file, but I don' want the end user doing this everytime. Here is the code:

Sheets("All Trunks&quot.Select
Range("A6&quot.Select
dfile = ActiveCell.Value
'This is where the path will be changed, if need be, by the end user'

Sheets("Sheet9&quot.Select
Range("A1&quot.Select
ChDir dfile
sfile = dfile & "*123.xls"
sfile1 = Dir(sfile)
Workbooks.Open FileName:=sfile1
'this is when the error occurs'

The error states that it cannot find or open the file, but it states the correct file. This is a little confusing for me.

I would appreciate any help or suggestions.

Thanks,
crpjaviman

 

[dsi]

Does the path in cell D6 end with a backslash? Is the file found with the Dir() command?