I'm writing a VBA macro that compares data between the currently open
worksheet and the one produced the previous week. Right now the user has
to manually open the previous worksheet, but I thought it would be nifty
if I automatically open it.

So my question is, given a target folder cLocation and a pattern
"foo*.xls", how can I get the name of the most recent (prior to today)
matching file in that location?

--
Peter B. Steiger
Cheyenne, WY
If you must reply by email, you can reach me by placing zeroes
where you see stars: wypbs.**1 at gmail.com.

It's hard to go wrong with John Walkenbach...
http://www.j-walk.com/ss/excel/tips/tip97.htm
-or-
My own VBA code, using the FileSystemObject, allows one to specify
the number of most recent files to return.
I'll post the code upon request.
--
Jim Cone
Portland, Oregon USA



"Peter B. Steiger"
wrote in message
I'm writing a VBA macro that compares data between the currently open
worksheet and the one produced the previous week. Right now the user has
to manually open the previous worksheet, but I thought it would be nifty
if I automatically open it.
So my question is, given a target folder cLocation and a pattern
"foo*.xls", how can I get the name of the most recent (prior to today)
matching file in that location?

--
Peter B. Steiger
Cheyenne, WY
If you must reply by email, you can reach me by placing zeroes
where you see stars: wypbs.**1 at gmail.com.

The reason I asked if the date was encoded in the filename is my fear that a
wrong file could be returned using the code cited. IF a user were to modify
an older file in the directory for some reason, then that file would have a
later "modified date" than the true last created file. There is an API
solution that can look as the actual creation date for the file, but if the
date is encoded in the file names, that would be a much easier solution.

--
Rick (MVP - Excel)


"Jim Cone" wrote in message
...
It's hard to go wrong with John Walkenbach...
http://www.j-walk.com/ss/excel/tips/tip97.htm
-or-
My own VBA code, using the FileSystemObject, allows one to specify
the number of most recent files to return.
I'll post the code upon request.
--
Jim Cone
Portland, Oregon USA



"Peter B. Steiger"
wrote in message
I'm writing a VBA macro that compares data between the currently open
worksheet and the one produced the previous week. Right now the user has
to manually open the previous worksheet, but I thought it would be nifty
if I automatically open it.
So my question is, given a target folder cLocation and a pattern
"foo*.xls", how can I get the name of the most recent (prior to today)
matching file in that location?

--
Peter B. Steiger
Cheyenne, WY
If you must reply by email, you can reach me by placing zeroes
where you see stars: wypbs.**1 at gmail.com.

Rick,
Thanks for that info. I have never paid any attention to file creation date
vs. last modified date and have always just used a last modified date.

I checked the help file for the Scripting Runtime library and found that the
File object does have both a DateCreated and a DateLastModified property.
I ran a my FSO code and the DateCreated files returned are different
(and earlier dated) then the DateLastModified files.

Maybe another optional argument for the function.
--
Jim Cone
Portland, Oregon USA
(don't look back, they might be gaining on you)




"Rick Rothstein"
wrote in message
The reason I asked if the date was encoded in the filename is my fear that a
wrong file could be returned using the code cited. IF a user were to modify
an older file in the directory for some reason, then that file would have a
later "modified date" than the true last created file. There is an API
solution that can look as the actual creation date for the file, but if the
date is encoded in the file names, that would be a much easier solution.
--
Rick (MVP - Excel)


"Jim Cone" wrote in message
...
It's hard to go wrong with John Walkenbach...
http://www.j-walk.com/ss/excel/tips/tip97.htm
-or-
My own VBA code, using the FileSystemObject, allows one to specify
the number of most recent files to return.
I'll post the code upon request.
--
Jim Cone
Portland, Oregon USA



"Peter B. Steiger"
wrote in message
I'm writing a VBA macro that compares data between the currently open
worksheet and the one produced the previous week. Right now the user has
to manually open the previous worksheet, but I thought it would be nifty
if I automatically open it.
So my question is, given a target folder cLocation and a pattern
"foo*.xls", how can I get the name of the most recent (prior to today)
matching file in that location?

--
Peter B. Steiger
Cheyenne, WY
If you must reply by email, you can reach me by placing zeroes
where you see stars: wypbs.**1 at gmail.com.

On Thu, 11 Jun 2009 22:01:02 -0400, Rick Rothstein sez:
IF a user were to modify
an older file in the directory for some reason, then that file would
have a later "modified date" than the true last created file.

Hmmm, I had not thought about that. As it happens, I do save the files
with the date encoded as part of the file name, but using text months
("foobar_April_13.xls"). I guess I could start from yesterday and work
backwards, looking for the first file that matches the encoded date in
the file name. Thanks!

--
Peter B. Steiger
Cheyenne, WY
If you must reply by email, you can reach me by placing zeroes
where you see stars: wypbs.**1 at gmail.com.

Is the date encoded in the file name in anyway?

--
Rick (MVP - Excel)


"Peter B. Steiger" wrote in message
. ..
I'm writing a VBA macro that compares data between the currently open
worksheet and the one produced the previous week. Right now the user has
to manually open the previous worksheet, but I thought it would be nifty
if I automatically open it.

So my question is, given a target folder cLocation and a pattern
"foo*.xls", how can I get the name of the most recent (prior to today)
matching file in that location?

--
Peter B. Steiger
Cheyenne, WY
If you must reply by email, you can reach me by placing zeroes
where you see stars: wypbs.**1 at gmail.com.