0

I am trying to import comma separated numbers in a cell in excel. It looks like this on web:

000002678,000002737,000002827,000004326,000008528

But as soon as I import it in excel it transforms to following:

2,678,000,002,737,000,000,000,000,000,000,000,000,000

How can I maintain the format like it is on web and not treat it as one big number?

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://localhost:65076/TestData", Destination:=Range( _
    "$A$1"))
    .Name = "Test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
user793468
  • 4,898
  • 23
  • 81
  • 126
  • What do you mean by "import ... in [E]xcel?" What do you mean by "on [the] [W]eb?" Are you manually copying and pasting text from a Web page into a cell? Are you automatically scraping Web page contents or accessing a RESTful API? Are you using the Excel automation API? If so, which function and with what parameters? – Jollymorphic Mar 22 '13 at 22:48
  • @Jollymorphic There is an import From web option in Excel "Data->Get External Data->From Web" Where you can specify a url and import data from a particular website. – user793468 Mar 22 '13 at 22:51
  • what version of excel do you use? i tested it in excel 2010 and worked. Selected the cell A1 and the Import from web then i put this url and checked the first div with numbers. Now i have those numbers comma seperated in A1 cell. – Nikitas Mar 22 '13 at 22:55
  • You need to set data separator as `comma` and set Data format as `text` to keep all leading zeros. – Peter L. Mar 23 '13 at 18:57
  • @PeterL. How to set data separator as comma? – user793468 Mar 23 '13 at 19:29
  • Use `Text to Columns` feature: http://stackoverflow.com/a/14285131/1953175 - once I faced the same problem. First import your data as text, then use macro recorder to get the code while doing that manually. – Peter L. Mar 23 '13 at 19:35
  • @PeterL. When you say import your data as Text, do you mean have excel cell formatted as Text? I import my data from a website. – user793468 Mar 23 '13 at 20:26
  • Yes, destination cells should be formatted as text. – Peter L. Mar 23 '13 at 21:42
  • @PeterL. Formatting Cell to Text and then importing from web displays: 2.678000002737E+39; Original Content from web: 000002678,000002737,000002827,000004326,000008528 – user793468 Mar 23 '13 at 22:47
  • @PeterL. I Use QueryTables.add to import data from a website in excel – user793468 Mar 23 '13 at 22:50

1 Answers1

0

@PeterL or user793468 Did you ever solve this? Because i have the exact same problem and the only solution i found was a workaround (that actually can work!)

Set Windows Region and Language settings: "Decimal Settings" from , to . and excel will not treat my case 016448,016501,017612,01... etc as one long number.

If you plan on running the script "autonomasly" then just have those settings in a virtual machine where you run the script.

AdamW
  • 1