locked
Convert HTML-table to Excel (office 2016) RRS feed

  • Question

  • Trying to convert an HTML-table to an Excel table. 

    The problem is that Excel changes the table in the following way:

    • Text converted to date
    • Numbers in parentheses are now negative.
    • Numbers are rounded or truncated.
    • All horizontal lines are removed.

    The following VBA fixes the issues with the text being converted to dates. But the other problems remain:

    'Sub importhtml()


    ' importhtml Macro
    '

    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;https://rasmusrhl.github.io/stuff", Destination:=Range("$A$1"))

            .Name = "stuff"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingAll
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = True
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False

        End With
    End Sub

    Is this at all possible to do? I believe it should be possible because if Excel interprets numbers in parentheses in a certain systematic way, it must mean that Excel does correctly parse them in the first place. I just want to stop it from doing anything to them after initial parsing. 

    Any ideas?

    Thursday, January 11, 2018 10:40 AM

All replies

  • Hi rasmushertzum,

    i try to make a test on my side and got the same result like yours.

    i made many changes in properties of ListObject.Add but i got similar result.

    then i try to record macro and try to import data using manual steps.

    i find that if i use that recorded macro then it imports data correctly.

    below is my recorded macro.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://rasmusrhl.github.io/stuff""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""tailnum"", type text}, {"""", type text}, {""Some text goes here. It is long and does not break Machine type (make) year"", type text}, {""Some text goes here. It is long and does not break Machi" & _
            "ne type (make) type"", type text}, {""Some text goes here. It is long and does not break Machine type (make) manufacturer"", type text}, {""Some text goes here. It is long and does not break"", type text}, {""Some text goes here. It is long and does not break Specification of machine model"", type text}, {""Some text goes here. It is long and does not break Specific" & _
            "ation of machine engines"", type text}, {""Some text goes here. It is long and does not break Specification of machine seats"", type text}, {""Some text goes here. It is long and does not break Specification of machine speed"", type text}, {""Some text goes here. It is long and does not break Specification of machine engine"", type text}, {""2"", type text}, {""Othe" & _
            "r text goes here Other variables s1"", type text}, {""Other text goes here Other variables s2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Table 0]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_0"
            .Refresh BackgroundQuery:=False
        End With
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 12
    End Sub
    

    you can try to create new workbook and add Sheet2 then run code above.

    you will get output like below.

    the difference i find that it uses OLEDB connection.

    so you can also try to use this macro or record your own macro then you can modify it for desired formatting.

    i find the solution using VBA but i did not get any useful information regarding this issue.

    so this work around can solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 12, 2018 2:51 AM
  • Hi rasmushertzum,

    Is your issue resolved?

    I find that, After creating this thread, You did not done any follow up on this thread.

    If your issue is solved then I suggest you to share your solution and mark it as an answer.

    If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

    If then after you have any further questions then you can let us know about it.

    We will try to provide further suggestions to solve it.

    I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

    So take appropriate steps to close this thread , If your issue is resolved.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 9:52 AM