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
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