Web Query - Unwanted Scientific Formatting

[Audri]

I'm performing a web query on a html file containing nbrs having an embedded "E". Excel formats the data as scientific; I need to have a text format. I've tried using a .txt extention on the file and preformatting the cell as text prior to performing the web query. I need to be able to work with the html file as it is currently formatted (ie: adding a ' in front of the data is not an option).

By the time Excel completes loading the sheet, it has already changed the VALUE of the data as well as the format. Is there a way to add a ' in front of the data during the Web Query operation, or to otherwise prevent conversion of the data?

Here is my code:

Sub BondsEquityAccts()

ActiveWorkbook.Worksheets.Select
Load LoadMsg
LoadMsg.Show (vbModeless)

'try to prevent scientific formating
'(didn't work)
'
Columns("C:C").NumberFormat = "@"

Application.ScreenUpdating = False

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://s390/SharedApplications/servlet/WIORDY1.TXT" _
, Destination:=Range("A1"))
.Name = "BondsEquityAccts"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.Refresh BackgroundQuery:=False
End With

'try again to prevent scientific formating
'(didn't work)
Columns("C:C").NumberFormat = "@"

Application.ScreenUpdating = True
LoadMsg.Hide

End Sub

 

[Al Chara]

Why don't you try to do a find search and replace all the "E" with another character string and then import the text to excel.