自动刷新上传的网页 [英] AutoRefresh Uploaded WebPage
问题描述
我有一个电子表格,每2分钟自动保存一次,它也自动重新发布为网页.
然后,此网页将作为此两分钟周期的一部分上载到网站空间.
很好,效果很好.
但是,我希望任何人都可以查看此页面以自动查看更新,而不必记住F5.
如果这是正常的网页,我可以在HEAD部分中放入<meta http-equiv=''refresh'' content=''2;url=''file_name or URL''>
这样的行.
但是,由于该页面是在Excel中自动创建的,因此无法包括在内,所以有人知道我该怎么做吗?
此处 [ ^ ].
如您所见,它显示了英格兰足球联赛的进球数和进球数后的更新,并实时显示了进球数.
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.
推荐答案
正如Jyothikarthik_N所说,我''编写了自定义程序以直接从Excel将数据保存到html文件中.
它不是完美的,但是显示了一个主意.
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.
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> </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> </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> </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> </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 ;)
这篇关于自动刷新上传的网页的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!