Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zoom makes text disappear on one computer but not another... | Excel Discussion (Misc queries) | |||
Inserting Text Makes Numbers Disappear! | Excel Discussion (Misc queries) | |||
Why do the preceding zeros disappear when I remove dashes? | Excel Discussion (Misc queries) | |||
Grouping selection in Pivot table makes all data disappear | Excel Discussion (Misc queries) | |||
HTML to Excel: preceding 0's disappear?! | Excel Discussion (Misc queries) |