Open file saved as today's date

L

Lift Off

In Excel 2002, I want to write code that uses a file that's been store
with the current/todays date. Can't figure out how to identify th
file name since it changes everyday.

Know it's got to be simple, but tried all combinations and it hang
up.

Workbooks.Open Filename:= _
"C:\Documents and Settings\LiftOff\Desktop\ & Date.xls"

TIA, Lift Of
 
A

Anders S

Lift Off,

This works for me:

Workbooks.Open Filename:= "C:\Documents and Settings\Anders\Desktop\" & date &
".xls"

HTH
Anders Silven
 
B

Bob Phillips

If the date is formatted, use something like

Workbooks.Open Filename:= "C:\Documents and Settings\Anders\Desktop\" & _
Format(Date, "yyyy-mm-dd") & ".xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Anders S said:
Lift Off,

This works for me:

Workbooks.Open Filename:= "C:\Documents and Settings\Anders\Desktop\" & date &
".xls"

HTH
Anders Silven
Click to expand...
 
L

Lift Off

Anders: Tried that but I guess the format of the file name is wrong.
Your method looks for mm/dd/yyyy. My file name is mmm/dd. I guess
can save the file differently, but I not such a long name. Any othe
suggestions?

TIA Lift Of
 
B

Bob Phillips

See my answer and adapt the format, but drop the /, filenames don't like it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

Lift Off

Thanks Bob, That worked and I'm able to open. But the code now hangs u
as elsewhere I try to activate the worksheet and it hangs up on th
date again, but I'm using the correct format. i.e. same format as
opened the workbook.

Code is: Windows(" & Format(Date, "mmm dd") & ".xls").Activate

Am I missing a space somewhere? The date is ''mmm dd" with out a das
between.

TIA

BTW, always wanted to ask.....where's Poole Harbour?
 
A

Anders S

Lift Off,
My file name is mmm/dd
Click to expand...
Is that really possible?

Use Bob's code. You may experiment with the format in the Immediate window to
get it right like
?format(Date, "mmm-dd")

Regards
Anders Silven
 
L

Lift Off

Anders: Sorry typo. My file name format is: mmm dd.xls

Windows(" & Format(Date, "mmm dd") & ".xls").Activate

I get a syntax error with the above code.

Lift Off
 
B

Bob Phillips

This code

Windows(" & Format(Date, "mmm dd") & ".xls").Activate

seems to be missing something, a single open quote should give you a compile
error. Make sure that you use the workbook name, not the fullname which
includes the path. But, you shouldn't need it, because as soon as you open
it, that workbook becomes the active workbook.

Poole Harbour is on the south coast of England, in the county of Dorset,
next to the Dorset World Heritage Coastline. The harbour is renowned as the
second largest natural harbour in the world (in terms of perimeter
distance), only beaten by Sydney harbour.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

Lift Off

Bob: This is the copied code:

Windows(" & Format(Date, "mmm dd") & ".xls").Activate

It does give a compile error and syntax error, highlighting the "mmm".


I open the workbook, go to others and need to come back and activat
the opened later. Basically, that's the flow.

Lift Of
 
C

Chris

Try using
intdate = Day(Date) & "_" & Month(Date) & "_" & Year(Date)

Workbooks.Open Filename:= _
"C:\Documents and Settings\LiftOff\Desktop\" & intdate
& ".xls"
 
B

Bob Phillips

The problem is that leading ".

IT is treating the (" & Format(Date, " as the workbook id, and then wonders
what the mmm is all about,so errors.

Try

Windows(Format(Date, "mmm dd") & ".xls").Activate

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top