Log in

View Full Version : [SLEEPER:] Is it possible to ask vba to write html code in to an html file?


ntupper
07-16-2010, 06:02 AM
I currently have the following code;

Sub RefreshData()
ActiveWorkbook.Save
Call SaveIt
End Sub

Sub SaveIt()
ThisWorkbook.Save
Application.OnTime Now + TimeSerial(0, 5, 0), "SaveIt"
Call SaveAsHtml
End Sub

Sub SaveAsHtml()
Workbooks("Project tracking report.XLSM").Activate
Dim wNew As Workbook, i As Integer
Dim wOld As Workbook
Application.ScreenUpdating = False
Set wOld = ActiveWorkbook
wOld.Sheets(1).Copy
Set wNew = ActiveWorkbook
For i = 2 To wOld.Sheets.Count
wOld.Sheets(i).Copy after:=wNew.Sheets(Sheets.Count)
Next
wNew.Sheets(1).Activate
wNew.SaveAs "http://xxxxxxxxxxxx", FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
wNew.Close
Set wNew = Nothing
Set wOld = Nothing
Application.ScreenUpdating = True
End Sub

This does three things; on a click event it saves the workbook then calls for the sheet to save at intervals (currently every 5 minutes). It then saves as a new html file on a web server. The problem is the web page is updated every 5 minutes but I am running a web dashboard that would also need to refresh at similar intervals. I have some html code (<meta http-equiv=refresh content="X; URL=/..../index.html">) that does this but it would need to be written in to the html file some how on the save event. Is it possible? P.S I am not able to download and use IE add ons that do similar due to a paranoid IT department!
Bob Phillips
07-16-2010, 06:55 AM
An HTML file is just a text file, so just open it as text, write in the extra line, and save it as text.
ntupper
07-16-2010, 07:39 AM
That would work but because it is a new file that is uploaded to the server every five minutes, would it mean I would need to keep doing this every five minutes?
Bob Phillips
07-16-2010, 08:02 AM
Yes, because you overwrite it every five minutes.
ntupper
07-16-2010, 08:09 AM
Thanks XLD but I may be stupid or missing something here - would that not defeat the object of adding the html code to the html file after it has saved if I need to open the file and add it after? I could just click the refresh button every five minutes on the browser but will not be at the PC that will be displaying the info... like I said I may be missing somehting!
ntupper
07-16-2010, 08:11 AM
Would it be possible to add the code in to the SaveAsHtml sub somehow so that it is already in the HTML file?
Kenneth Hobs
07-16-2010, 08:26 AM
It sounds like you want to add text to an html file? Since it is a text file, after you save it, use code to modify it. You could post a sample of before and after. Make it as short as possible.

Two background methods use FSO and VBA's Open() method. You can look in VBA's help for Open's syntax.

You would probably want the code to read to a point, add that to a new text (html) file, write your new text, and then write the rest of the other text file's data to this new text file.

e.g.

Sub NameTest()
Dim ff As Integer, sDir1 As String, sDir2 As String, sFN1 As String, sFN2 As String
sDir1 = ThisWorkbook.Path & "\": sFN1 = "NameTest.txt"
sDir2 = ThisWorkbook.Path & "\NameTest\": sFN2 = "SomeOtherName.txt"
ff = FreeFile
Open sDir1 & sFN1 For Output As #ff
Print #ff, "Hello World!"
Close #ff
If Dir(sDir2, vbDirectory) = "" Then MkDir sDir2
If Dir(sDir2 & sFN2) <> "" Then Kill (sDir2 & sFN2)
Name sDir1 & sFN1 As sDir2 & sFN2
End Sub
Bob Phillips
07-16-2010, 08:26 AM
I would have thought so, just insert the line(s) where appropriate in the sheet.
Bob Phillips
07-16-2010, 08:27 AM
Thanks XLD but I may be stupid or missing something here - would that not defeat the object of adding the html code to the html file after it has saved if I need to open the file and add it after? I could just click the refresh button every five minutes on the browser but will not be at the PC that will be displaying the info... like I said I may be missing somehting!

Write code to do it.
ntupper
07-19-2010, 02:27 AM
Thanks all for your replies, the sample code is below;

Before.


<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 12">
<link rel=File-List
href="Glasgow%20restack%20project%20tracking%20report_files/filelist.xml">
<![if !supportTabStrip]>
<link id="shLink" href="Glasgow%20restack%20project%20tracking%20report_files/sheet001.htm">
CODE CONTINUES.............

After.


<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=refresh content="; 60; " >
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 12">
<link rel=File-List
href="Glasgow%20restack%20project%20tracking%20report_files/filelist.xml">
<![if !supportTabStrip]>
<link id="shLink" href="Glasgow%20restack%20project%20tracking%20report_files/sheet001.htm">
CODE CONTINUES.............
Bob Phillips
07-19-2010, 03:40 AM
Is that FYI, or are you asking us to write the code to generate that?
ntupper
07-19-2010, 03:46 AM
I am posting the code samples of before and after as suggested by Kenneth, I tried using the code posted but ended with an error at the following point ;

If Dir(sDir2, vbDirectory) = "" Then MkDir sDir2.
The edited code is now;

Sub NameTest()
Dim ff As Integer, sDir1 As String, sDir2 As String, sFN1 As String, sFN2 As String
sDir1 = ThisWorkbook.Path & "\": sFN1 = "Glasgow restack project tracking report.txt"
sDir2 = ThisWorkbook.Path & "\NameTest\": sFN2 = "Project tracking report.txt"
ff = FreeFile
Open sDir1 & sFN1 For Output As #ff
Print #ff, "<meta http-equiv=refresh content="; 60; " >"
Close #ff
If Dir(sDir2, vbDirectory) = "" Then MkDir sDir2
If Dir(sDir2 & sFN2) <> "" Then Kill (sDir2 & sFN2)
Name sDir1 & sFN1 As sDir2 & sFN2
End Sub
Bob Phillips
07-19-2010, 03:59 AM
I would just use



On Error Resume Next
MkDir sDir2
Kill sDir2 & sFN2
On Error Goto 0
ntupper
07-19-2010, 04:11 AM
Thank you XLD but now get an error on; Open sDir1 & sFN1 For Output As #ff
ntupper
07-19-2010, 04:22 AM
OK, I think I have cracked it now with everyones help, I will post the updated code once completed. Thanks all for your help.
ntupper
07-19-2010, 07:43 AM
:banghead: OK, my eureka moment didn't quite have the result that I thought, I now write a text file with the extra html code only i.e. <meta http-equiv=refresh content="; 60; " > !!!!! Think I may be back to square 1.
Bob Phillips
07-19-2010, 10:40 AM
Try this



Sub UpdateTextFile()
Dim MyString, aryText As Variant
Dim NotFirst As Boolean
Dim filenum As Long
Dim i As Long
filenum = FreeFile()
Open "c:\MyHTML.html" For Input As #filenum
ReDim aryText(1 To 1)
i = 0
Do While Not EOF(1)
Input #filenum, MyString
If Left$(MyString, 5) = "<meta" Then
If Not NotFirst Then
i = i + 1
ReDim Preserve aryText(1 To i)
aryText(i) = "<meta http-equiv=refresh content=""; 60; "" >"
NotFirst = True
End If
End If
i = i + 1
ReDim Preserve aryText(1 To i)
aryText(i) = MyString
Loop
Close #filenum
filenum = FreeFile()
Open "c:\MyHTML.html" For Output As #filenum
For i = LBound(aryText) To UBound(aryText)
Print #filenum, aryText(i)
Next i
Close #filenum
End Sub
Kenneth Hobs
07-19-2010, 05:39 PM
Sub UpdateTxtFile()
Dim MyString As String
Dim fileName As String, fileName2 As String
Dim filenum As Long, filenum2 As Long
Dim i As Long
fileName = "g:\MyHTML.html"
fileName2 = Environ("temp") & "\MyHTML.html"
filenum2 = FreeFile()
Open fileName2 For Output As #filenum2
filenum = FreeFile()
Open fileName For Input As #filenum
Do While Not EOF(filenum)
i = i + 1
Input #filenum, MyString
Print #filenum2, MyString
If i = 5 Then Print #filenum2, "<meta http-equiv=refresh content=""; 60; "" >"
Loop
Close #filenum
Close #filenum2
FileCopy fileName2, fileName
Kill fileName2
End Sub
ntupper
07-20-2010, 01:32 PM
Thanks guys, I will give these solutions a try and let you know how I get on. I appreciate all of your help and vast knowledge as always:bow: