Open file saved as today's date

[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

 

[Anders S]

Lift Off,

This works for me:

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

HTH
Anders Silven

 

[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)

Lift Off,

This works for me:

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

HTH
Anders Silven

 

[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

 

[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)

 

[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?

 

[Anders S]

Lift Off,

My file name is mmm/dd

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

 

[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

 

[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)

 

[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

 

[Chris]

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

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

 

[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)