0

I am looking to set up a macro to save 6 different worksheets as individual HTML files daily with the current date. I can find the code for saving as current date, and code for saving as HTML, I cannot figure out how to make them work together. My first of the 6 sheets is named Admin. Thank you for any help you can give me learning this one.

Here is the code I am currently trying and get errors with:

Sub SaveAsHTML()
'
' SaveAsHTML Macro
'
' Keyboard Shortcut: Ctrl+h
'
  Dim newFile As String, fName As String
    fName = Range("A1").Value
    newFile = fName & " " & Format$(Date, "mmddyy") & ".htm"
    With ActiveWorkbook.PublishObject.Add(xlSourcePrintArea, _
        "J:\Service Technology\Daily Stats\CSC Daily Report\Archive\Admin\fname" _
        , "Admin", "", xlHtmlStatic, "CSCDailyReport_29344", "")
        .Publish (True)
        .AutoRepublish = False
    End With
End Sub
Community
  • 1
  • 1
Rich
  • 1

1 Answers1

0

fName needs to be concatenated to the end of the path and PublishObject should be PublishObjects:

...
    With ActiveWorkbook.PublishObjects.Add(xlSourcePrintArea, _
    "J:\Service Technology\Daily Stats\CSC Daily Report\Archive\Admin\" & fName _
...
Ross McConeghy
  • 874
  • 2
  • 7
  • 16
  • I made that change. I am getting a Run_Time error '438' Object doesn't support this property or method. it highlights the "J:\Service Technology\Daily Stats\CSC Daily Report\Archive\Admin\" & fName _ , "Admin", "", xlHtmlStatic, "CSCDailyReport_29344", "") section. – Rich Aug 20 '14 at 15:57
  • @Rich Answer revised, `PublishObjects` is the collection that you are using the `Add` method of. – Ross McConeghy Aug 20 '14 at 16:40