Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Web Query - Unwanted Scientific Formatting

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •