Problems with Wildcards and VBA

[Guest]

I am having problems with building paths from my variables. I want to use the
wildcard "*" and have it substitute characters between 2 known text strings.
ie. using it to create dynamic directory changing based upon the month of the
year. I am currently using "DateRef = XLMonth & "/" & "*" & "/2005"" with
success, but I cannot get "LeFldr = Left(LeRef, 4) & "_" & "*" & ".xls"" to
work. I simply get the following result . . . "7014_*.xls"

My code is as follows:
Dim LeRef As String
Dim LePth As String
Dim Lefldr as String
Dim DateRef as String
XlMonth as String
DateRef = XLMonth & "/" & "*" & "/2005"
LeFldr = Left(LeRef, 4) & "_" & "*" & ".xls"

Results in "\\v1sacpdofc2\cpdprojects\CIS Financials\QLE\LE\F05
LEs\7014_*.xls\Jan 05\7014_R1B F05 LE_CIS x.xls"

 

[Bob Phillips]

Helen,

I may be misundesrtanding, but if you put an * in a string, you get an *.

If you want the current month, use

LeFldr = Left(LeRef, 4) & "_" & Month(Date) & ".xls"

or

LeFldr = Left(LeRef, 4) & "_" & Format(Month(Date),"00") & ".xls"

or ev en

LeFldr = Left(LeRef, 4) & "_" & Format(Date,"mmm")) & ".xls"

 

[Guest]

Actually Bob I'm having problems with LeFldr = Left(LeRef, 4) & "_" & "*" &
".xls"
as part of a directory path variable. I have 7 projects that I monitor and
do stats for. I was looking for a way to manage the fact that the folder name
changes each month to something like:
"L:\CIS Financials\QLE\LE\F05 LEs\Jan 05\70174 R1B Reconciliation\7014_R1B
F05 LE_CIS x.xls"

I wanted to write a macro that would reference each of the files in
succession, and pull out some data and put it into a third sheet. The problem
is that when I set the path using a wild card such as "*", I actually get a
"*" instead of the wildcard function. Therefor "LeFldr = Left(LeRef, 4) & "_"
& "*" & ".xls" actually equals "7014_*.xls"