Is it possible to ask vba to write html code in to an html file? [Archive]

View Full Version : 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

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: