Excel import - formatting question

[Patrick Boyd]

Hi,

We've some code that spits out an html table from our application and opens
it within excel. One of our clients has recently been having a problem with
date formats (operations in different regions of the world).

We changed our code to output all the dates in the format dd mmm yyyy (e.g.
31 Mar 2006), they've come back with a problem that our report is fine for
the first couple of hundred lines in excel, the date columns are correctly
formatted as dates, but after that line, all the date formatting is lost, and
just displayed as 'text'. It's also impossible to change the cell format back
to date...

(the line the format is lost on has identical dates in many cases to the
previous line...)

Anybody ever seen anything like this? I'm stumped...

 

[Bernie Deitrick]

Patrick,

I have no experience with HTML import, but it should be easy enough to use a macro to fix the dates.
see the example below, which fixes dates in column C - this assumes that there are no numbers other
than dates in column C, and that cell D1 is otherwise empty....

HTH,
Bernie
MS Excel MVP

Sub FixDates()
Dim myDates As Range
Dim myConst As Range

Set myDates = Columns("C:C")
Set myConst = Range("D1") ' pick a blank cell


myDates.NumberFormat = "dd mmm yyyy"
myConst.Value = 1
myConst.Copy
myDates.SpecialCells(xlCellTypeConstants, 23).PasteSpecial _
Operation:=xlMultiply
myDates.NumberFormat = "dd mmm yyyy"
myConst.Clear

End Sub

 

[Patrick Boyd]

While i'm sure that would work, i was really wondering why this works fine
for half the sheet and then dies...

Also these spreadsheets are built up dynamically, so we would need some
clever code to decide where the appropriate update-requiring cells would be.

 

[Bernie Deitrick]

Patrick,

From your description of the problem, it sounds like the source of the error is the export step,
which isn't done by Excel. Take a look in the HTML code for a problem file.... perhaps you'll spot
something in one of the control codes...

HTH,
Bernie
MS Excel MVP