I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.

I am using the the following macro:

Sub SaveRangeWeb2()
    'Creates a web page without interactivity from specified range
    ActiveWorkbook.PublishObjects.Add _
        SourceType:=xlSourcePrintArea, _
        Filename:=ActiveWorkbook.Path & "\Sample1.htm", _
        Sheet:=ActiveSheet.Name, _
        HtmlType:=xlHtmlStatic

    ActiveWorkbook.PublishObjects(1).Publish (True)
End Sub

I am having some problems with this though.

First, it only publishes what ever is defined as the print range on the first sheet in my workbook, not the currently active sheet. I want it to publish the print area of the currently active sheet.

Second, once the macro runs one time, no matter what I name the output file name, it still uses sample1.htm. So when I run the macro the first time it creates the file called sample1.htm. However, if I change the name of the output filename in the macro to sample2.htm, it still names it sample1.

Any help would be much appreciated!