opening a file that will have wildcards in VBA

[needhelpwithVBA]

i need to open a .dat file in an excel using a wildcard. this is what
i have so far.

Dim aname
Set aname = range("A1")
dim mypath

mypath = "c:\documents" & Format(Int(Now()), "yyyy") & "\lists" &
Format(Int(Now()), "mm-yyyy") & "\" & ANAME & "######" & ".dat"

however, no matter how many times i try to open up the file
workbooks.open filename:= mypath

the error that keeps appearing is run time error '1004'
which is

'c:\documents\2004\lists10-2004\<aname>######.dat' could not be found.
check the spelling of the file name and verify that the file location
is correct.

the thing is, when i change the ###### to the actual numbers, the
macro works fine. however, i need the macro to recognize these files
that have arbitrary numbers in the file name with at least the
beginning <aname> part of the file name and the file extention (.dat).
it seems that the wildcard function just does not work. whether it be
###### for numbers, ?????? for characters, or * for anything. is this
some VBA bug or is there some solution?

help please.

 

[Dick Kusleika]

NHWVBA

You can't use wildcards in Workbooks.Open or Workbooks.OpenText. What if
more than one file has that format? Do you want to open all of them? Just
the first one?

You might look into the Dir function. You can use wildcards with Dir, but
only * and ?, not #. To open the first file that looks like your string

Dim aname as String
Dim bname as String
dim mypath as String

aname = range("A1").Value & "??????.dat"

mypath = "c:\documents" & Format(Int(Now()), "yyyy") & "\lists" &
Format(Int(Now()), "mm-yyyy") & "\"

bname = Dir(mypath & aname)

If Len(baname) = 0 Then
'no file found
Else
Workbooks.OpenText mypath & bname
End If