Insert Meta Refresh tag when publishing as web page

S

savarin

New Member
Joined
Jun 6, 2003
Messages
9
I am using the following code to publish a portion of my worksheet as a web page:

Code: 
Sub SavetoWeb()

    Sheets("worksheet").Select
    ActiveWindow.ScrollRow = 1
    Range("B1:E15").Select
    ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Documents and Settings\savarin\My Documents\worksheet.html", "worksheet", "$B$1:$E$15" _
        , xlHtmlStatic, "astrodata_3987", "").Publish (True)
End Sub

I would like to insert a MetaRefresh tag into the html as the sheet is saved.
Does anyone know how to do that?
 
Sort by date Sort by votes
Hi, welcome to the board. I think it can be done by using some standard open/read/write routines of files. What's the tag that you want to include ?
 
Upvote 0
Thank you for the welcome.

The tag I wanted to insert is:

Code: 
<META HTTP-EQUIV="Refresh" CONTENT="10; myfile.htm">
 
Upvote 0
whoops,

I think I inserted my meta refresh tag into my last email.

The tag I wish to include is:
Code: 
<META HTTP-EQUIV="Refresh" CONTENT="10; drive.htm">
 
Upvote 0
Ok, the following code does it. I used the Scripting object instead, which seemed easier than scanning each line of the resulting html file. It does go after the HEAD tag, right ?

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> SavetoWeb()

    <SPAN style="color:#00007F">Const</SPAN> RefreshTag <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "< META HTTP - EQUIV=""Refresh"" CONTENT=""10; drive.htm"" >"
    <SPAN style="color:#00007F">Const</SPAN> sPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C:\Documents and Settings\savarin\My Documents\worksheet.html"
    <SPAN style="color:#00007F">Const</SPAN> sPathTemp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C:\Documents and Settings\savarin\My Documents\worksheet2.html"
    <SPAN style="color:#00007F">Const</SPAN> Sheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "worksheet"
    <SPAN style="color:#00007F">Const</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B1:E15"
    
    <SPAN style="color:#00007F">Dim</SPAN> FSO <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>   <SPAN style="color:#007F00">'New FileSystemObject</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> St <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> FSO = CreateObject("Scripting.FileSystemObject")    <SPAN style="color:#007F00">'New FileSystemObject</SPAN>
    ActiveWorkbook.PublishObjects.Add(xlSourceRange, sPath, Sheet, _
            Rng, xlHtmlStatic, "astrodata_3987", "").Publish <SPAN style="color:#00007F">True</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> FSO.OpenTextFile(sPath, 1) <SPAN style="color:#007F00">'ForReading</SPAN>
        St = .ReadAll
        .Close
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    FSO.CreateTextFile sPathTemp, <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">With</SPAN> FSO.OpenTextFile(sPathTemp, 2) <SPAN style="color:#007F00">'ForWriting</SPAN>
        .Write Left$(St, InStr(1, St, "<head>", 1) + Len("<head>" & vbCr))
        .Write RefreshTag & vbCr
        .Write Right$(St, Len(St) - InStr(1, St, "<head>", 1) - Len("<head>"))
        .Close
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    FSO.DeleteFile sPath
    FSO.MoveFile sPathTemp, sPath
    
    <SPAN style="color:#00007F">Set</SPAN> FSO = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

In the RefreshTag constant, put the one that you want (I did, but had to put some spaces so it appeared fine on the board)
 
Upvote 0
Wow! Awesome.

It does need to be before the </head> tag. I couldn't figure out in your script how you place it before or after the <body> tag.

:-D
 
Upvote 0
Very awesome!

I need to put the code inside the head tag. I couldn't figure out from your script how to do that.

Sorry if I am posting duplicates. My browser hangs up on some of the posts.
 
Upvote 0
It is supposed to write it AFTER that... and I just saw that the code didn't get pasted correctly. Let's try this:

Code: 
Sub SavetoWeb()
    Const RefreshTag As String = "<META HTTP-EQUIV=""Refresh"" CONTENT=""10; drive.htm"">"
    Const sPath As String = "C:\Documents and Settings\savarin\My Documents\worksheet.html"
    Const sPathTemp As String = "C:\Documents and Settings\savarin\My Documents\worksheet2.html"
    Const Sheet As String = "worksheet"
    Const Rng As String = "B1:E15"
    
    Dim FSO As Object   'New FileSystemObject
    Dim St As String
    Set FSO = CreateObject("Scripting.FileSystemObject")    'New FileSystemObject
    ActiveWorkbook.PublishObjects.Add(xlSourceRange, sPath, Sheet, _
            Rng, xlHtmlStatic, "astrodata_3987", "").Publish True
    
    With FSO.OpenTextFile(sPath, 1) 'ForReading
        St = .ReadAll
        .Close
    End With
    
    FSO.CreateTextFile sPathTemp, True
    With FSO.OpenTextFile(sPathTemp, 2) 'ForWriting
        .Write Left$(St, InStr(1, St, "<head>", 1) + Len("<head>" & vbCr))
        .Write RefreshTag & vbCr
        .Write Right$(St, Len(St) - InStr(1, St, "<head>", 1) - Len("<head>"))
        .Close
    End With
    
    FSO.DeleteFile sPath
    FSO.MoveFile sPathTemp, sPath
    
    Set FSO = Nothing
End Sub
 
Upvote 0
I am very glad I found this place. I can't believe how quickly this issue got resolved.

I think I'll start hanging out here.

:-D
 
Upvote 0
There were a couple things I did to make this work. The first is that I changed the string to

Const RefreshTag As String = "****** http-equiv=""Refresh"" CONTENT=""30"">"

and then I specified the head tag text to find with the InStr command:
.Write Left$(St, InStr(1, St, "<head>", 1) + Len("<head>" & vbCr))
.Write RefreshTag & vbCr
.Write Right$(St, Len(St) - InStr(1, St, "<head>", 1) - Len("<head>"))After adding that this worked like a charm.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Compose Email VBA Help
Replies
1
Views
363
Logit
Logit
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
1K
Domenic
D
A
  • Question Question
publish object error 1004
Replies
6
Views
535
Skyybot
Skyybot
G
  • Question Question
Email from Excel query
Replies
0
Views
408
gmazza76
G
Antonio Costa
  • Solved
VBA to automate sending emails and copy date/time and subject for other sheet
Replies
3
Views
680
DanteAmor
DanteAmor

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top