opening excel file -> date format problem: DD/MM/YYYY vs MM/DD/YYYY

[yung]

Hi,

I am using Excel 2000, programming with VB6.

I have a HTML file "Temp.xls" (yes, in xls extension) with a table only.
There is a cell containing a date:
..... <tr><td>07/12/2004</td></tr>...
Note that this date means Dec 7, 2004 (in DD/MM/YYYY format).
When I open this file in Excel, it shows the date format well as above.
But when I write code to open it:

Dim xlsApp as Object
Dim FilePath as String
.....
FilePath = "C:\Temp.xls"
Set xlsApp = Nothing
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Workbooks.Open (FilePath)
....

I find that the date becomes 12/07/2004, which is not what I want.

The date fornat in my regional setting is dd/mm/yyyy already. How can I
solve this problem?

Thanks a lot!

Yung

 

[broogle]

I think you have to format that cell in your code.

Sheets("mysheet").Range("myrange").Value = Format(Now(), "dd-mm-yy")

 

[Tom Ogilvy]

I see you are using xl2000. I think in that case, you would need to find
the date and convert it - formatting the cell after the fact as suggested
will have no effect.

If it displays as

12/07/2004, then you can use vba's conformance to US English to your
advantage (untested, but try)

ActiveCell.Value = ActiveCell.Text

in your code after you open the file (ActiveCell represents a reference to
the cell with the date)