Force Visual Basic to copy data from HTML file to Excel AS TEXT RRS feed

  • Question

  • I've been using the Workbooks.OpenText <filename> function (full argument shown below) to open a HTML file which contains a table. Everything is working except that some of the entries have the form 123456E-00, which Excel recognises as a formula and converts to 1.23+05 (123456). Once the file has been opened in Excel, the correct entry is lost, so changing the cell format to Text is useless.

    How do I ensure that Visual Basic views all the entries as text, rather than numbers?

    Workbooks.OpenText <filename>, Origin _
        :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
        Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2)), TrailingMinusNumbers:=True

    Friday, March 21, 2014 2:21 AM

All replies

  • I assume you are importing this, right.  Turn on the Macro recorded, on step 3 of 3, select Text, do the import, and look at the code.  That should give you what you want.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, May 2, 2014 2:52 PM