Data Issues from html files

RaunLGoode

Registered User.
Local time
Today, 05:36
Joined
Feb 18, 2004
Messages
122
I need to import SAP data in an HTML file into Excel, but there seems to be a data type issue.

When I select a cell I get the value of the numbers in the cell, but if I try to use the cells in a calculation, or try to Sum etc. the cells, I get a value of zero or the "#VALUE!" error. The Data comes in as "General" type

If I hand enter the value of a cell it works fine. I tried to use Format Cell to change data types with no luck. I also tried to Copy >Paste Special , also with no luck.
My two questions are:
1) What is causing this problem?
2) How can/should I modify the data into a usable data type?
 
Can't speak as to why it does what it does, but I've experienced it too. If I remember correctly, the way I fixed it was to put in a formula in the cell next to it of
=A1*1
and then autofilled down. Then, copied that column and used paste special to paste the values, then deleted the second column.
 
More

I just tried to bring the data in using Data>Import and the data worked fine in Calc's....Weird!
 
Followup ?

Importing the data from the html file eliminates the data type issue. But I need to automate this for the end user. I recorded the following Macro todo this, but I would like to modify the section in bold so that a browse window is opened. I Know I need to put something like:
[fileToOpen = Application.GetOpenFilename("HTML Files (*.HTML), *.HTML")] but I cant get the code to work. Could somebody show me the error of my ways?


Sub importData()
'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///G:/EB_Information/Excel/12KST1A.html.%252.html", Destination _
:=Range("A1"))

.Name = "12KST1A.html.%252"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
 
Followup ?

Importing the data from the html file eliminates the data type issue. But I need to automate this for the end user. I recorded the following Macro todo this, but I would like to modify the section in bold so that a browse window is opened. I Know I need to put something like:
[fileToOpen = Application.GetOpenFilename("HTML Files (*.HTML), *.HTML")] but I cant get the code to work. Could somebody show me the error of my ways?


Sub importData()
'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///G:/EB_Information/Excel/12KST1A.html.%252.html", Destination _
:=Range("A1"))

.Name = "12KST1A.html.%252"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
 

Users who are viewing this thread

Back
Top Bottom