Workbook.open function - no .xls?

[Dave]

Hi everyone.

Our archives are stored in a rather interesting format, in
that the "aba" for 07/29/2003 is stored as
aba.csv.2003_07_29

This presents a problem for me when trying to open
archived data manually, because the workbooks.open
function ALWAYS adds an .xls to the end of the statement
below, which does not exist.

I am using: Workbooks.Open Filename:="aba.csv." & Year
(Range("P2").Value) & "_" & Month(Range("P2").Value) & "_"
& Day(Range("P2").Value)

Can anyone assist me in forcing EXCEL not to add the .xls
to the filename (as above)?

Thanks.

Dave

 

[Bob Phillips]

Dave,

Try
Workbooks.Open Filename:="aba" & Year(Range("P2").Value) & "_" &
Month(Range("P2").Value) & "_" & Day(Range("P2").Value) & ".csv"

 

[Dave Peterson]

It seemed to work ok for me (xl2002 and win98).

But I think the real problem wasn't with the extension. I think it was with
your formatting.

You write that your filename is like this: aba.csv.2003_07_01

But your format statement looses the leading zeros of the month and day.

Instead of:
Workbooks.Open Filename:="aba.csv." & _
Year(Range("P2").Value) & "_" & _
Month(Range("P2").Value) & "_" & _
Day(Range("P2").Value)
maybe:
Workbooks.Open Filename:="aba.csv." & _
Format(Year(Range("P2").Value), "0000") & "_" & _
Format(Month(Range("P2").Value), "00") & "_" & _
Format(Day(Range("P2").Value), "00")
or even simpler:

Workbooks.Open Filename:="aba.csv." & _
Format(Range("P2").Value, "yyyy_mm_dd")