AutoRefresh Uploaded WebPage

Question

3.00/5 (2 votes)

See more:

I have a spreadsheet that is autosaved every 2 minutes, and it also autorepublishes as a webpage.
This webpage is then uploaded to the webspace as part of this two minute cycle.

This is fine and works well.

However, I would like to have anyone looking at this page to see the update automatically, without having to remember to F5.

If this was a normal webpage I could put in a line like <meta http-equiv='refresh' content='2;url='file_name or URL'> in the HEAD section.

However as the page is created automatically in Excel then this cannot be included so does anyone have any idea how I can do this?

Here[^] is the table.
As you can see it shows the English Football League Tables and updates after every goal is scored, showing the tables live and as it happens.

Posted 29-Jan-12 0:55am

Dalek Dave

Updated 29-Jan-12 0:58am

v2

Add a Solution

Comments

[no name] 29-Jan-12 7:10am

Why not try a different approach?

Keep the webpage as it is, just have it referring this Spreadsheet datasource. Have a notification/poll-based mechanism to check data-change, and re-bind the data.

This looks more elegant, just a suggestion.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-03-23T11:48:00

As Jyothikarthik_N wrote, i've write custom procedure to save data into html file direct from Excel.
It's not perfect, but shows an idea.

VB

Option Explicit

'call below procedure on Workbook_Open() event
Sub StartTick()
'save data to html file every 10 second
SaveMyData
End Sub

'call below procedure on Workbook_BeforeClose() event
Sub StopTick()
'stop saving
Application.OnTime LatestTime:=Now + TimeSerial(0, 0, 1), Procedure:=ThisWorkbook.Name & "!SaveMyData", Schedule:=False
End Sub

';)
Sub SaveMyData()
ExportDataToHTML ThisWorkbook.Path & "\league.html"
Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 10), Procedure:=ThisWorkbook.Name & "!SaveMyData", Schedule:=True
End Sub

'export data to html
Sub ExportDataToHTML(dstFileName As String)
Dim sTmp As String, numFile As Integer
Dim r As Integer, c As Integer
Dim wsh As Worksheet

On Error GoTo Err_ExportDataToHTML

Set wsh = ThisWorkbook.Worksheets(1)
numFile = FreeFile
Open dstFileName For Output As #numFile
    Print #numFile, "<html>"
    'head
    Print #numFile, "<head>"
    Print #numFile, "<meta http-equiv=Content-Type content='text/html; charset=windows-1252'>"
    Print #numFile, "<meta http-equiv='refresh' content='2;url='file_name or URL'>"
    Print #numFile, "<meta name=Generator content='CustomExcelGenerator'>"
    Print #numFile, "</head>"
    'body
    Print #numFile, "<body bgcolor='aqua'>"
    'main table
    'contains only one row, but many columns
    Print #numFile, "<table name='MainContainer' align='center'>"
    Print #numFile, "<tr>"
    'first column: Premier column
    Print #numFile, "<td><p align='center' bgcolor='yellow'><b>Premier</b></p>"
        Print #numFile, "<table name='Premier' border=1>"
        Print #numFile, "<tr bgcolor='orange'><th>Team</th><th>Pl</th><th>P</th><th>GD</th><th>GS</th></tr>"
        For r = 3 To 20
            Print #numFile, IIf(r Mod 2 = 0, "<tr bgcolor='white'>", "<tr bgcolor='gray'>")
            For c = 2 To 6 'B to F
                Print #numFile, "<td>" & wsh.Cells(r, c).Value & "</td>"
            Next c
            Print #numFile, "</tr>"
        Next r
        Print #numFile, "</table>"
    Print #numFile, "</td>"
    'end of first column of MainContainer table
    '--- separator ---
    Print #numFile, "<td>&nbsp</td>"
    '--- end of separator ---
    'second column: Championship
    Print #numFile, "<td><p align='center' bgcolor='yellow'><b>Championship</b></p>"
        Print #numFile, "<table name='Championship' border=1>"
        Print #numFile, "<tr bgcolor='orange'><th>Team</th><th>Pl</th><th>P</th><th>GD</th><th>GS</th></tr>"
        For r = 3 To 20
            Print #numFile, IIf(r Mod 2 = 0, "<tr bgcolor='white'>", "<tr bgcolor='gray'>")
            For c = 8 To 12
                Print #numFile, "<td>" & wsh.Cells(r, c).Value & "</td>"
            Next c
            Print #numFile, "</tr>"
        Next r
        Print #numFile, "</table>"
    Print #numFile, "</td>"
    'end of second column
    '--- separator ---
    Print #numFile, "<td>&nbsp</td>"
    '--- end of separator ---
    '3 column: League 1
    Print #numFile, "<td><p align='center' bgcolor='yellow'><b>League 1</b></p>"
        Print #numFile, "<table name='League1' border=1>"
        Print #numFile, "<tr bgcolor='orange'><th>Team</th><th>Pl</th><th>P</th><th>GD</th><th>GS</th></tr>"
        For r = 3 To 20
            Print #numFile, IIf(r Mod 2 = 0, "<tr bgcolor='white'>", "<tr bgcolor='gray'>")
            For c = 14 To 18
                Print #numFile, "<td>" & wsh.Cells(r, c).Value & "</td>"
            Next c
            Print #numFile, "</tr>"
        Next r
        Print #numFile, "</table>"
    Print #numFile, "</td>"
    'end of 3 column
    '--- separator ---
    Print #numFile, "<td>&nbsp</td>"
    '--- end of separator ---
    '4 column: League 2
    Print #numFile, "<td><p align='center' bgcolor='yellow'><b>League 2</b></p>"
        Print #numFile, "<table name='League2' border=1>"
        Print #numFile, "<tr bgcolor='orange'><th>Team</th><th>Pl</th><th>P</th><th>GD</th><th>GS</th></tr>"
        For r = 3 To 20
            Print #numFile, IIf(r Mod 2 = 0, "<tr bgcolor='white'>", "<tr bgcolor='gray'>")
            For c = 20 To 24
                Print #numFile, "<td>" & wsh.Cells(r, c).Value & "</td>"
            Next c
            Print #numFile, "</tr>"
        Next r
        Print #numFile, "</table>"
    Print #numFile, "</td>"
    'end of 4 column
    '--- separator ---
    Print #numFile, "<td>&nbsp</td>"
    '--- end of separator ---
    '4 column: Blue Square Prem
    Print #numFile, "<td><p align='center' bgcolor='yellow'><b>Blue Square Prem.</b></p>"
        Print #numFile, "<table name='BSP' border=1>"
        Print #numFile, "<tr bgcolor='orange'><th>Team</th><th>Pl</th><th>P</th><th>GD</th><th>GS</th></tr>"
        For r = 3 To 20
            Print #numFile, IIf(r Mod 2 = 0, "<tr bgcolor='white'>", "<tr bgcolor='gray'>")
            For c = 26 To 30
                Print #numFile, "<td>" & wsh.Cells(r, c).Value & "</td>"
            Next c
            Print #numFile, "</tr>"
        Next r
        Print #numFile, "</table>"
    Print #numFile, "</td>"
    'end of 4 column
    '=== row 2 - generation time ===
    Print #numFile, "<tr><td colspan=10>Last update: " & Now & "<td></tr>"
    Print #numFile, "</table>"
    'end of MainContainer table

    'end of body
    Print #numFile, "</body>"
    'end of html document
    Print #numFile, "</html>"
Close #numFile

Exit_ExportDataToHTML:
    On Error Resume Next
    Set wsh = Nothing
    'in case of write error
    Close #numFile
    Exit Sub

Err_ExportDataToHTML:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_ExportDataToHTML
End Sub

Have a nice day ;)