Is there a way to use wild cards for the "709" part of the "200709" string
since the last two digits change every month and the 7 will change at year
end. I tried the wildcards noted in excel VBA help
("...PCC*.xls","...200???.xls","...200###") on just the file name (I haven't
even tried the folder name yet) and none of them worked.

Workbooks.Open _
("Q:\LATIN AMERICA\SABRE\2007\DATA 200709\BCDLA ACTIVE PCC 200709.XLS")

first = true
do
if first = true then
filename = dir("c:\temp\*709.xls")
first = false
else
filename = dir()
if filename < "" then


' enter your code - filename contains the name of the file found
' filename doesn't include path, so you may have to add path
end if
loop while filename < ""

"ksh" wrote:

Is there a way to use wild cards for the "709" part of the "200709" string
since the last two digits change every month and the 7 will change at year
end. I tried the wildcards noted in excel VBA help
("...PCC*.xls","...200???.xls","...200###") on just the file name (I haven't
even tried the folder name yet) and none of them worked.

Workbooks.Open _
("Q:\LATIN AMERICA\SABRE\2007\DATA 200709\BCDLA ACTIVE PCC 200709.XLS")

A good answer Joel. I found it useful. I amended it very slightly, as I
needed to open all files from two folders/paths. So, same code, just set the
path (my_path) before the loop, and then concatinate the path and file name.
At the end of the loop, change my_path to the next path name, eg c:\temp2,
and then run the loop again. Can do this as many times as you need. For my
purposes I simpley use a wildcard for the file names, as i wanted o open all
the files in the folder, though of course in your original example you used
part of the file name to limit which files are opened. The principle remains
the same though.

first = true
my_path = "c:\temp1"
do
if first = true then
filename = dir("c:\temp\*.xls")
my_file = my_path & filename
first = false
else
filename = dir()
if filename < "" then

' enter your code - filename contains the name of the file found
' filename doesn't include path, so you may have to add path
end if
loop while filename < ""

Tony





"Joel" wrote:

first = true
do
if first = true then
filename = dir("c:\temp\*709.xls")
first = false
else
filename = dir()
if filename < "" then


' enter your code - filename contains the name of the file found
' filename doesn't include path, so you may have to add path
end if
loop while filename < ""

"ksh" wrote:

Is there a way to use wild cards for the "709" part of the "200709" string
since the last two digits change every month and the 7 will change at year
end. I tried the wildcards noted in excel VBA help
("...PCC*.xls","...200???.xls","...200###") on just the file name (I haven't
even tried the folder name yet) and none of them worked.

Workbooks.Open _
("Q:\LATIN AMERICA\SABRE\2007\DATA 200709\BCDLA ACTIVE PCC 200709.XLS")

Maybe this is what you want to do this

dim strMonth, strYear, strDate as string

strMonth=format$(Month(Now), "00")
strYear=format$(Year(Now), "####")
strDate=strYear+strMonth

Workbooks.Open _
("Q:\LATIN AMERICA\SABRE\2007\DATA 200709\BCDLA ACTIVE PCC
+"strDate+".XLS")


Faisal...

On Nov 2, 3:44 pm, ksh wrote:
Is there a way to use wild cards for the "709" part of the "200709" string
since the last two digits change every month and the 7 will change at year
end. I tried the wildcards noted in excel VBA help
("...PCC*.xls","...200???.xls","...200###") on just the file name (I haven't
even tried the folder name yet) and none of them worked.

Workbooks.Open _
("Q:\LATIN AMERICA\SABRE\2007\DATA 200709\BCDLA ACTIVE PCC 200709.XLS")