Hello all.

I'm running Excel 2000 SP3 on Win98SE.
I have several separate HTML documents generated by Papyrus Design Group
"Grand Prix Legends" which contain tables of Track Names, Lap Times and
Dates. I'm trying to import this data into Excel for analysis and
presentation, but I'm having problems.
If I manually open the HTML documents in Excel, I can easily copy & paste
data from the HTML page into my tables, and the results are fine.
However, I have tried to automate this process by recording a macro to:
1. open an HTML document,
2. select a range,
3. copy the data,
4. select the destination sheet and range,
5. paste the data,
6. close the HTML doc.
This code is run from a command button on the destination sheet, using the
"click" event.
When the macro is run, some (not all!) of the dates in the table have their
format corrupted.
I've cut the macro down until there is only the "Workbooks.Open" statement
left, and it appears that it is this open statement which is corrupting the
data. After running this statement on its own, the open HTML document is
displayed showing the corrupted dates.
In the original HTML documents, the dates are all in dd/mm/yy format, but
when Excel opens the documents with "Workbooks.Open", some of the dates get
switched to mm/dd/yy format (e.g. 09/02/04 becomes 02/09/04).
I have checked time and again that all the regional date settings I can find
in Office 2000 and Win98 are set to "U.K. English" or equivalent, and yet
this still happens. I am stumped.
Can anyone give me a fix for this problem? And can anyone explain why I
should get different results when manipulating the data manually vs using a
macro?

Thanks in advance for any help offered.

--
Keith Ballard

Hi Keith;

Several thing could be going on. Probably the best way to
work around the problem is to include a macro to reformat
the dating. It might look like this:

Sub try()
Selection.NumberFormat = "mm/dd/yy;@"
'or
Selection.NumberFormat = "dd/mm/yy;@"
End Sub

depending on which way you want it to go.

Also, Excel 2000 and above do not open html documents on
as friendly bases as 97. I would do a test and open a
test document where the values get corrupted in 2000 with
Excel 97 (Make sure it has the SP2) patch applied.

If it opens correctly in 97 but not 2000 then there are
tag errors comming from "Grand Prix Legends". I have run
into this before and found that I had missing <tr or <td
tags.

This is most likely what is going on.

Thanks,

Greg


-----Original Message-----
Hello all.

I'm running Excel 2000 SP3 on Win98SE.
I have several separate HTML documents generated by
Papyrus Design Group
"Grand Prix Legends" which contain tables of Track Names,
Lap Times and
Dates. I'm trying to import this data into Excel for
analysis and
presentation, but I'm having problems.
If I manually open the HTML documents in Excel, I can
easily copy & paste
data from the HTML page into my tables, and the results
are fine.
However, I have tried to automate this process by
recording a macro to:
1. open an HTML document,
2. select a range,
3. copy the data,
4. select the destination sheet and range,
5. paste the data,
6. close the HTML doc.
This code is run from a command button on the destination
sheet, using the
"click" event.
When the macro is run, some (not all!) of the dates in
the table have their
format corrupted.
I've cut the macro down until there is only
the "Workbooks.Open" statement
left, and it appears that it is this open statement which
is corrupting the
data. After running this statement on its own, the open
HTML document is
displayed showing the corrupted dates.
In the original HTML documents, the dates are all in
dd/mm/yy format, but
when Excel opens the documents with "Workbooks.Open",
some of the dates get
switched to mm/dd/yy format (e.g. 09/02/04 becomes
02/09/04).
I have checked time and again that all the regional date
settings I can find
in Office 2000 and Win98 are set to "U.K. English" or
equivalent, and yet
this still happens. I am stumped.
Can anyone give me a fix for this problem? And can anyone
explain why I
should get different results when manipulating the data
manually vs using a
macro?

Thanks in advance for any help offered.

--
Keith Ballard
.