Office 2007 Excel Macro to save Active Worksheet to HTML by name

[daviller]

I have run through the Macro recorder and saved this macro to convert
an existing worksheet to HTLM in a specific file share. What I am
trying to do is save the 'active' worksheet. I have a multi-sheet
workbook, and I need to publish each sheet as it's name to an HTML
file. I can make it work with individual macros, but I can't get a
macro to detect the active sheet and use it's name as the HTML file:

'************** Start Code
Sub TestToHtml()
'
' TestToHtml Macro
' TestDescription
' Keyboard Shortcut: Ctrl+m
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Users\daviller\Documents\temp.htm", "C", "", xlHtmlStatic,
"temp_26389", "" _
)
.Publish (True)
.AutoRepublish = False
End With
End Sub

'********************* End Code

I've tried replacing the "C" (worksheet name) with activesheet.name
and other variations, but none of this works. any help would be
greatly appreciated. my end game is to have a macro that my users can
Ctrl+e and it publishes an HTML file to a specific directory based on
the worksheet name.

Cheers++
David

 

[Tim Williams]

Sub AllSheets()
Const HTML_PATH As String = "C:\Users\daviller\Documents\<sheet>.htm"

Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
Replace(HTML_PATH, "<sheet>", s.Name), _
s.Name, "", xlHtmlStatic, "temp_26389", "")
.Publish (True)
.AutoRepublish = False
End With
Next s

End Sub

Tim