• 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...

  • 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

Status
Not open for further replies.

Chihiro

Excel Ninja

Attachments

  • TableToHTML.xlsb
    64.9 KB · Views: 303
Last edited:
@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:
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...
 
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).
 
@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
 
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.
 
Quick sample using tool mentioned above.
ConvertToJson is the function needed here.

Code:
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:
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!
 
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:
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
 
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
 
1. I didn't use table name, as I used code to set table object using a cell that belongs to table.
Code:
Set tbl = UserCell.ListObject

You can, remove code from "On Error Goto..." to above line and replace it with...
Code:
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).
 
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!
 
Hello
Good work...
Sorry but this it no the idea of responsive view. This is hide columns..
Responsive means to convert rolls to column....
 
? Working code is in the first post.
It's more of academic curiosity project than practical application in real life situations.
For actual web site. I'd recommend leveraging ASP .NET, Ajax, Python, etc for more robust data manipulation.
 
I am using the Search version which using the html shared in the first thread of this discussion.

I tried to change != to = to achieve a small different requirement.

I have some columns where I would like to return values if it is not equal to the search full word.

Like

APPLE
APPLE
MANGO
MANGO
MANGO
PINE
PINE
PEAK
PEAK
PEAK

then if i type APPLE, i wanted to get rows with the column which did not have apple.

so result would be
MANGO
MANGO
MANGO
PINE
PINE
PEAK
PEAK
PEAK

I could not achieve this. If possible, kindly share tips on how to tweak the table in the html.
 
Status
Not open for further replies.
Back
Top