Macro to Publish Print Area from Active Sheet to Web Page

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!

Does anybody have any ideas here?

It seems like I am close, but just missing something simple...

Thanks for this. However, I can't get this to run through.

Just to be clear, I do not want to hard code the Sheet name or the print range in the macro.

I would like the macro to automatically publish whatever has been defined as the print range for the currently selected sheet. If the print area is changed, then the macro would automatically publish that new print area. If the user selects a different Sheet, the print area that has been defined on that sheet would be automatically published.

Also, it is a nice value add, but there is no need to verify in the browser what will be or has been published -- so that does not need to be in the macro.

The following macro from my initial post almost does this perfectly except that it only will look at the print area of the first sheet in the workbook -- rather than the currently selected sheet. And the other problem is that if I change the output file name in the macro and rerun it, it still publishes the file with the original name.

So it seems that my 2 problems are: first, the active sheet is not being properly selected in the macro. And second, the filename seems to be buffered and that needs to be cleared at the end of each run -- or something like that :-)

I appreciate any further help!

What do you mean, can't get this to run through? Are you getting errors?

1). Pass the SheetName into the routine

2). I believe that there is a function that will return the selected cells. It's used in getting the source below

3). The best thing you can do is keep the published HTML filename the same, and remove the old one before you publish the new one.

See the following adjusted code

Private Sub openBrowser2(sheetName As String)

    ' set sheet, sheet range & select the sheet
    Sheets(sheetName).Select    ' must select the sheet or .Publish will not work

    ' set the output filename
    previewFilename = "\Print Preview.htm"
    
    ' remove the previous published HTML file
    On Error Resume Next
    Kill (previewFilename)

    ' create the html output file
    ' Source:= select the cells in that sheet
    ActiveWorkbook.PublishObjects.Add _
        SourceType:=xlSourceRange, _
        Filename:=previewFilename, _
        Sheet:=sheetName, _
        Source:=Sheet1.Cells.Select, _
        HtmlType:=xlHtmlStatic
    
    ActiveWorkbook.PublishObjects(1).Publish True

    ' preview the output file
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
            .MenuBar = 0
            .Toolbar = 0
            .StatusBar = 0
            .navigate previewFilename
            .Visible = 1
    End With
   
End Sub

tony h, i am getting an error and cannot run this. Run time error '1004'. Application-defined or object-defined error.

In Tonyh's example, since he used "xlSourcePrintArea", you must set the print range on the sheet before executing the code. Go to the sheet of choice and select a range, then select the File...Print Area...Set Print Area

In my example, you'll have to setup a sub that calls the one I have coded with the sheetname like this...

sub start()
openBrowser2("Sheet2")
end sub

OR at the top of the code just add the code to activate the sheet of your choice

Sheets("Sheet2").Activate

Let me know if this doesn't work.

Hi,

you didn't say where the 1004 error was occuring. A typical reason would be referring to a sheet that doesn't exist ie a spelling mistake.

There are a couple of good ways to try and identify what your code is doing. Apologies if you already know this. I will go through the long version first as this is, in my opinion, a "proper" way to observe your code necessary for good testing.

First is to put a "breakpoint" in your code. Click on the first line of your subroutine (or any executable line) and press F9 The line will then be given a "brown" background. This means that when this line of code is to be executed processing waits.

Second add these two "debug.print" lines in the code as here, I will explain later why I have put the two in lines:

For Each var In VArray()
    Debug.Print var(0), var(1), var(2)
    Debug.Print "¦" & var(0) & "¦", "¦" & var(1) & "¦", "¦" & var(2) & "¦"
    
    If Worksheets(var(0)).Cells(3, 8) >= var(1) Then
        Sheets(var(2)).Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
Next

now run your code. When processing hits the breakpoint the code pane window comes up and the line is highlighted in yellow. Now "step through" the code pressing F8. This executes one line at a time so you can see what is happening.

When the "debug.print" lines are executed information appears in the "immediate window" if you cannot see the "immediate window" type <ctrl>G or make sure it is selected from the "view" menu.

In the "immediate window" you will get a pair of lines thus:
Sheet11 2 Page (2)
¦Sheet11¦ ¦2¦ ¦Page (2)¦

The advantage of the second line is that you can see if there are any leading or trailing spaces which might be causing trouble.

Press F8 until you get the 1004 error. This will appear in a form. Press [DEBUG]. This will take you to the line that is causing the problem. By looking at the values, or hovering the mouse over the variable which will display the value, and checking the spelling carefully with the actual names of the sheets you should be able to determine the problem more accurately.

And, have learnt more about what the code is doing.

Alternatively (if you like to wear a 10 gallon hat and spurs): just run the code and follow the instructions in the last paragraph.

Notes:
1. you can have as many breakpoints as you like
2. breakpoints are not saved so reopening the spreadsheet will not have breakpoints in place
3. you can cancel a breakpoint by clicking on the original and pregging F9

4. learn about F5, and debugging in general



Hope this help

thanks. I have a print area set on Tony H. macro. let me work through the debug feedback and get back to you guys. I appreciate the detailed input as I am bordering on novice at best!

JWM6, I will try your additional call.

thanks

I'm sorry, I can't work out the debugger. When I add in the debug print lines I can't get to work. I hit F9 on the block of code po= etc. and it selected the whole paragraph and when I ran it and debugged an arrow pointed to the last line

HtmlType:=xlHtmlStatic)

I am attaching a simple test spreadsheet with Tony H macro in it to see if that helps.

I still need to try JWM6 feedback.

thanks.

Pretty much ignore my last post. I was answering a different question and got the two replies muddled up!

I'll have a look at your spreadsheet.

PS The reason why the F9 highlighted the whole block of text is because it effectively one statement split over several lines.

Just a note on your attached spreadsheet: It has 20 published objects defined. I have listed them below. Type 2 is the print area, type 4 is a range.

I haven't used publish objects much so someone else maybe better at helping out on this one. I will have a look as it interests me but don't expect anything too quickly.

number of objects:= 20

Debug.Print objpo.Sheet, objpo.Filename, objpo.DivID, objpo.SourceType, objpo.Source, objpo.Title

Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_31058 2
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_16065 2
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_30560 2
Sheet1 C:\Print Preview.htm Book1_14957 4 $A$1:$K$55
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_22978 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_26037 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_26103 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_26167 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_26186 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_26199 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_26222 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample2.htm Book1_32767 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample2.htm Book1_7880 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample2.htm Book1_11411 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\My Documents\Print Preview.htm Book1_6725 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\My Documents\PrintPreview.htm Book1_9274 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample2.htm Book1_29157 4 Print_Area
Sheet1 C:\Documents and Settings\jd150006\Desktop\test\Sample2.htm Book1_31568 4 Print_Area
Sheet2 C:\Documents and Settings\jd150006\Desktop\test\Sample1.htm Book1_21060 4 Print_Area
Sheet2 C:\Documents and Settings\jd150006\Desktop\test\Sample2.htm Book1_10003 4 Print_Area

this may be part of the problem. i wonder why there is a history. it seems like each time i have published the print ranges that it is getting stored in history.

strange.

the core of this is no matter what sheet I am on, i am trying to just publish the print range that has been defined on the sheet.

Okay, I have this working. It has no hard coded sheet names in it. It basically publishes the Print_Area of the currently selected sheet to the directory that the workbook resides in and names the published htm file as the same name of the selected sheet.

All I need to do is add an error check to ensure a print range has been defined on the currently selected sheet, and if not, to stop the macro and give a message saying that "No print range is defined. Please define a print range and rerun the macro."

Sub PrintRangePublish()
'
    
    ActiveSheet.Select
    ActiveSheet.PageSetup.PrintArea = "Print_Area"
    ActiveWindow.SelectedSheets.PrintPreview
      
    With ActiveWorkbook.PublishObjects.Add(xlSourcePrintArea, _
        ActiveWorkbook.Path & "\" & ActiveSheet.Name & ".htm", ActiveSheet.Name, "", _
        xlHtmlStatic, ActiveWorkbook, "")
        .Publish (True)
        .AutoRepublish = False
    
    End With

End Sub