Saving multiple worksheets as individual Web pages

  • Thread starter Thread starter PDelano
  • Start date Start date
P

PDelano

I receive a workbook each month with 15 worksheets. I
need to save each individual worksheet as a Web page to
post to our Intranet, so I end up having to Save As Web
page 15 times. Is there an easier or more automated way?

Thanks for your help.
 
record saving one as a macro then edit it to see the code
for how to do it, then play around with that :

for x=1 to 15
sheets(x). then the code for the saving as
next x

somehting like that.
 
One of the problems with recording this one that I've found, is using
the "saveas" method, I get those folders with the .html files too, not
just the .html file. I like the publish method better for just getting
the web page, but no folders with files in them...here is the code I
use:


Code:
--------------------

Sub saveHtml()

Dim mySht As Variant
Dim shtNme As String

For Each mySht In ActiveWorkbook.Sheets
shtNme = ActiveSheet.Name
mySht.Activate
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Documents and Settings\David Morrison\Desktop\" & shtNme & ".htm", _
shtNme, xlHtmlStatic)
.Publish (True)
.AutoRepublish = False
End With
Next

End Sub
--------------------


What this is doing, is for each sheet in the active workbook, it's
selecting the sheet, and then publishing it to my desktop using the
sheet name as the file name. You would just need to change the path of
the file. It will save your files with whatever you happen to name your
sheets as...it doesn't matter what you name them...if you have hidden
sheets in the workbook you don't want to have saved...just add an if
statement to only run when the sheet is visible...the line of code
would look like this:


Code:
--------------------

Sub saveHtml()

Dim mySht As Variant
Dim shtNme As String

For Each mySht In ActiveWorkbook.Sheets

If mySht.Visible = True Then
shtNme = ActiveSheet.Name
mySht.Activate
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Documents and Settings\David Morrison\Desktop\" & shtNme & ".htm", _
shtNme, xlHtmlStatic)
.Publish (True)
.AutoRepublish = False
End With
End If

Next

End Sub
--------------------


Hope this helps or at least points you in the right direction...

Have a good night,

Dave M.
 
Back
Top