I have been trying to write a routine to publish and update worksheet data on a web page on our company Intranet. I used the macro recorder to create a code fragment to achieve this (see below).

    With ActiveWorkbook.PublishObjects.Add(xlSourcePrintAre a, _
        "I:\General\01 Euro 2004 Sweepstake - Latest Positions.htm", "Positions", "", xlHtmlStatic, "Euro 2004_14332", _
        "")
        .Publish (True)
        .AutoRepublish = False
    End With

This works fine when run from the normal macro menu. I wanted to include a button with an Active x control on the actual sheet that could run the code. I inserted the routine call in the Button_Click event but when the publish routine runs it stops at the .Publish line with Error 1004. I have tried pasting the Publish code directly into the event handler but nothing makes any difference.

Could someone tell me what I am doing wrong?

This may sound a little strange, but I've run into similar difficulties in the past myself.

I believe that the problem you're having here is that your Button_Click event is specific to the page in which you've written the code. Create a module and put the code in there, and use the Button_Click event to call the sub from the module - this should solve your problem.

Hope that helps, if not I'll try to clarify with an example or something.


Ed.
Ne auderis delere orbem rigidum meum!