1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Table to Responsive HTML Table

Discussion in 'The Vault' started by Chihiro, Oct 17, 2016.

  1. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935

    Attached Files:

    Last edited: Oct 18, 2016
  2. PP3321

    PP3321 Active Member

    Messages:
    367
    @Chihiro
    Thank you so much for sharing!

    My boss loved it so much that she now asked me to use the data of 10,000 rows and 20 columns.

    When I run the macro, it crushed. It was 'Not Responding'.

    I looked at the code and I can see that it is looping each cell.
    Code (vb):
    For i = 1 To tbl.DataBodyRange.Rows.Count
    But is there a limit to the number of rows?

    I know with 10,000 rows it will be a big file.
    I wonder what are the implications of using larger data-set...
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    Depends on few things like RAM, Processor etc.

    In my test scenario, I had about 5,000 rows and 15 columns. Took about 3 min to process. I have 32Gb RAM, but 4Gb limit with Excel 2016 32bit (Processor is i5).

    One another method is to use native export to HTML method... but this may not help, as this method will bloat file size with unnecessary "style" applied to each cell.

    Theoretical limit is integer size, ie. 32,767 in above code sample. Replace with Long type and it can increase though not practical.

    Couple of other suggestions...

    1. Convert to Simple HTML table using 3rd party software/service (there's few good free ones out there). Then add/modify to make it responsive (you can use VBA to read & modify).

    2. Split operation into manageable size (i.e. export to several text file) then merge.

    If you are not married to Excel. Another method is to load the table to some database (MS SQL for example). Convert table to JSON text format. Then load to HTML using AJAX. Advantage being, you can make the HTML truly dynamic, by setting up jobs and JavaScript functions (reading from latest data set).
    Chirag R Raval and PP3321 like this.
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    A question, how is your HTML file used. Is it emailed or hosted on server?
  5. PP3321

    PP3321 Active Member

    Messages:
    367
    @Chihiro

    Thank you so much for your reply...!

    I am thinking these 3 options...

    1. Save the html file on the shared drive.
    2. On SharePoint
    3. Send it as email attachment every month
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    With your data size I'd recommend 1st option.

    If going with SharePoint, you can convert Excel table directly into SharePoint list via import Spreadsheet function.

    I'd not recommend option 3.

    If you have Internal website using IIS. I'd suggest using following tool to parse JSON (requires MS Scripting Runtime reference).
    https://github.com/VBA-tools/VBA-JSON/releases

    And use ajax to load.

    If you need further help on this, please start new thread or start a conversation. Attaching workbook with 10 rows of sample data.
    PP3321 likes this.
  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    Quick sample using tool mentioned above.
    ConvertToJson is the function needed here.

    Code (vb):
    Sub Test()
    Dim x As Variant
    Dim tbl As ListObject
    Dim oPath As String: oPath = "C:\Test\JSonTest.txt"
    Dim intFF As Integer: intFF = FreeFile()
    Set tbl = ActiveSheet.ListObjects("Table1")
    x = tbl.DataBodyRange.Value
    Open oPath For Output As #intFF
    Print #intFF, "{" & vbNewLine & """" & "data:" & """" & " " & ConvertToJson(x, 2) & vbNewLine & "}"
    Close #intFF
    End Sub
    This will generate JSON file with data only. Now you will need HTML like below.
    Replacing "Column#" with Column Header.

    HTML:
    <!DOCTYPE html>
    <html>
    <head>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
        <style type="text/css" class="init">

        </style>
        <script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-1.12.3.js">
        </script>
        <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js">
        </script>
        <script type="text/javascript" class="init">

    $(document).ready(function() {
        $('#example').DataTable( {
            "ajax": "\JSon2.txt"
        } );
    } );

        </script>
    <table id="example" class="display" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>Column1</th>
                    <th>Column2</th>
                    <th>Column3</th>
                    <th>Column4</th>
                    <th>Column5</th>
                    <th>Column6</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>Column1</th>
                    <th>Column2</th>
                    <th>Column3</th>
                    <th>Column4</th>
                    <th>Column5</th>
                    <th>Column6</th>
                </tr>
            </tfoot>
        </table>
    </body>
    </html>    
    Place both HTML and JSON file on server/machine running IIS (see link below to set up IIS to host simple Intranet site). Load file in browser and you will see table with CSS applied.

    https://social.technet.microsoft.co...re-iis-to-host-a-simple-intranet-website.aspx

    To add filter/search function, you'll need to combine it with the function found in the module uploaded earlier (jsFunc1 or jsFunc2).

    Edit: In my test, I exported table with 11,000 rows & 22 columns. Took about 7 sec to export as JSON text file.
    Last edited: Oct 18, 2016
  8. PP3321

    PP3321 Active Member

    Messages:
    367
    @Chihiro,
    Wow thank you so much!
    I need to learn about IIS.
    I will try & get back to you.
  9. Gil Araújo

    Gil Araújo New Member

    Messages:
    3
    Gretings Chihiro

    I believe this is an old thread but i found your code so useful i was compeled to register and write you a message asking for a little help.

    The code you do does EXACTLY what i was needing i have a small couple questions to ask.
    a) Would it be possible to change the code in order to automatize the process, as in bypass the form prompts. Where the sheet, Format, and output are hard coded.
    b) If that is possible, you think you would be so kind to point me in the right direction on where to look at the code to make such a change?

    Best regards, and awesome work!
    Chirag R Raval likes this.
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    Thanks for kind comments. :)

    a) Yes.

    b) Refer to original thread where this was discussed (follow link in my initial post here).
    You'll see where inputbox is used to get a cell that belongs to a table. You could replace that part with hard coded cell reference (which belongs to table).

    There are two files I uploaded in the thread. You could pick and choose which to use.
    In both files, you should look in "Module1".

    There you fill find following, which you can change to suite your need.
    Code (vb):
    On Error GoTo NoSelection
    Set UserCell = Application.InputBox(Prompt:="Select a cell within a table", Title:="Cell Select", Type:=8)

    NoSelection:
    If Err.Number = 424 Then
        MsgBox "Please select a cell"
        Exit Sub
    End If
    Chirag R Raval likes this.
  11. Gil Araújo

    Gil Araújo New Member

    Messages:
    3
    Thank you very much! i think i got it

    I am just struggling with a small thing.
    I cant find anywhere in the VBA code where the table name is defined.
    I am using the 1st example on the original thread, "AgentContactList" i cant see where that is hardcoded.

    A 2nd question i have if i can abuse your patience is this.
    Supose i have 10 sheets in a file. i believe i can multiply the Module 1 code with diferent names, then have a function that calls each sub one after the other? Or is there a more elegant way to do so in a automated way.

    Ultimately my goal is to generate the HTML every 5 mins so they are uploaded to a website and give Currency prices updated every 5 mins.

    Thank you and any advise would be apreciated.

    Regards
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    1. I didn't use table name, as I used code to set table object using a cell that belongs to table.
    Code (vb):
    Set tbl = UserCell.ListObject
    You can, remove code from "On Error Goto..." to above line and replace it with...
    Code (vb):
    Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("AgentContactList")
    2. No I would not recommend replicating Sub with different name. I'd just use code to loop through sheets and ListObjects and use variable to construct export path and file name.

    I wouldn't recommend using VBA to update HTML every 5 min. I'd use something more robust for that sort of scheduled repeated operation.

    Which, really should be housed in a server.

    Several methods:
    1. Use PHP to query data from database and update.

    2. Use Python or R to update json format file and use that to update table.

    3. Use PowerBI to query data from database or cloud service and publish. Using custom visual or slicer to filter data on the fly.

    Third method requires several layers of set up and not as straight forward as first 2 methods. You'll need to have data stored in MS SQL server 2012 or later, or have linked server set up with MS SQL server 2012 or later as staging server. Then set up direct query mode to load data. Data can be refreshed at 15 min interval, but can force refresh at shorter interval by refreshing browser window.

    Any further question, I'd recommend starting new thread in VBA forum with your requirement with reference to this thread (hyperlink).
  13. Gil Araújo

    Gil Araújo New Member

    Messages:
    3
    Many many Thanks
    A small issue is the infrastructure is already stabilished so i have little to work with.

    I think your help was priceless in putting me into the right direction.

    Thank you once more!
  14. Servlet

    Servlet New Member

    Messages:
    1
    Hello
    Good work...
    Sorry but this it no the idea of responsive view. This is hide columns..
    Responsive means to convert rolls to column....
  15. Rynis

    Rynis New Member

    Messages:
    3
    And if you use an online converter Will help?

Share This Page