opening html table in Excel makes preceding 0's disappear??!!
 

I am programing in VB6.

I have an HTML file with a table inside. There are cells with values like
01, 02, 003,...
like this:
....
<tr<td01</td</tr
<tr<td02</td</tr
<tr<td003</td</tr
....
I change this file's extension .xls.

When I try to open this file in Excel by writing program, the data appear to
be without the preceding 0's. (i.e. they become 1, 2, 3, ...) It seems that
the excel worksheet has change the numberformat of my data automatically
upon file open.

My question is: How can I prevent this format changing from happening when I
open the file in Excel? (suppose I CANNOT change the data in the HTML table
beforehand)

For your reference, I write something like this in VB:

Dim xlsApp as Object
Dim xlsWS as Object
...
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Workbooks.Open (App.Path & "\Temp.xls") ' after the code
runs, the excel file is loaded already
Set xlsWS = xlsApp.ActiveSheet
...


Thanks!

Yung

(P.S. I wrote a similar post in microsoft.public.excel.misc. But since my
problem involves programming, I re-post the question here.)

opening html table in Excel makes preceding 0's disappear??!!
 

Yung,

Excel is interpreting the values as numbers. You need to force text
interpretation. I've done it below by changing the html and writing to a new
file since you say you can't change the original data.

Sub Control()
Dim rngCell As Range
Dim strPath As String
Dim strNewPath As String

strPath = "e:\my documents\my excel files\temp\TestHTML.html"
strNewPath = "e:\my documents\my excel files\temp\Temp.html"

ReplaceInFile strPath, "<td", "<td'", strNewPath
Workbooks.Open strNewPath

'the import doesn't work 100% correctly
'it just puts the literal value in including the preceding single quote
'so re-enter the data one cell at a time and this time Excel gets it right
For Each rngCell In ActiveSheet.UsedRange
rngCell.Value = rngCell.Value
Next rngCell

End Sub

Sub ReplaceInFile(strPath As String, strReplace As String, strWith As
String, _
Optional strNewPath As String)

Dim lFile As Long
Dim strInput As String

lFile = FreeFile
Open strPath For Binary Access Read Write As lFile
strInput = String$(LOF(lFile), Chr$(32))
Get lFile, , strInput
strInput = Replace(strInput, strReplace, strWith)

If strNewPath = "" Then

Put lFile, 1, strInput
Close lFile

Else

Close lFile
lFile = FreeFile
Open strNewPath For Binary Access Write As lFile
Put lFile, 1, strInput
Close lFile

End If

End Sub

Robin Hammond
www.enhanceddatasystems.com

"yung" wrote in message
...
I am programing in VB6.

I have an HTML file with a table inside. There are cells with values like
01, 02, 003,...
like this:
...
<tr<td01</td</tr
<tr<td02</td</tr
<tr<td003</td</tr
...
I change this file's extension .xls.

When I try to open this file in Excel by writing program, the data appear
to
be without the preceding 0's. (i.e. they become 1, 2, 3, ...) It seems
that
the excel worksheet has change the numberformat of my data automatically
upon file open.

My question is: How can I prevent this format changing from happening when
I
open the file in Excel? (suppose I CANNOT change the data in the HTML
table
beforehand)

For your reference, I write something like this in VB:

Dim xlsApp as Object
Dim xlsWS as Object
...
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Workbooks.Open (App.Path & "\Temp.xls") ' after the code
runs, the excel file is loaded already
Set xlsWS = xlsApp.ActiveSheet
...


Thanks!

Yung

(P.S. I wrote a similar post in microsoft.public.excel.misc. But since my
problem involves programming, I re-post the question here.)