Problem with PublishObject in Excel VBA

[BluByU]

The code below returns a run-time error 1004 on the Publish method. I don't know what VB is complaining about. Thanks in advance for your help.

Set objRange = ActiveSheet.Range("A1:Q33")
objcoord = objRange.Address
Set objPublish = ActiveWorkbook.PublishObjects.Add
(xlSourceRange, strFileName, ActiveSheet.Name, _
objcoord, xlHtmlStatic, "", "")

objPublish.Publish False

 

[SkipVought]

Hi,

This ran sucessfully for me...

    Dim objRange As Range, objPublish As PublishObject, objcoord As String
    Set objRange = ActiveSheet.Range("A1:Q33")
    objcoord = objRange.Address
    Set objPublish = ActiveWorkbook.PublishObjects.Add _
        (xlSourceRange, _
        ThisWorkbook.Name, _
        ActiveSheet.Name, _
        objcoord, _
        xlHtmlStatic, _
        "", _
        "")
        
    objPublish.Publish False

Notice the declarations.

Skip,
[sub]
[red][/red]
[/sub]

 

[BluByU]

Thanks for the response. However, the "objPublish.Publish False" statement still returns a "1004" run time error. I have entered the code just as in your example. Any other suggestions on what might be going wrong?

 

[BluByU]

The full error message is: Run-time error '1004' Method 'Publish' of object 'PublishObject' failed.

 

[BluByU]

Can someone please help? I really need to get this working. Thanks in advance.

 

[SkipVought]

Please post you code again as you now have it.

Skip,
[sub]
[red][/red]
[/sub]

 

[BluByU]

Well, I just got it to work. here is what I had to do. The VBA code was triggered by a command button click on the spreadsheet. I read somewhere else that the command button couldn't have focus in order for the "button click" event to work properly, so I changed the property on the button.

Afterwards, I was still getting the run-time error, but I noticed that the files were being written to the path that I specified. So, I just put in an "On Error Resume Next" statement to suppress the error, and it now seems to work.

Thanks for your help, here is the code:

strFileName = "C:\Temp\SBS_WorkSheet" & ThisWorkbook.Sheets( "Blank Work Request Form ").Cells(1, 3).Value & ".htm"

ThisWorkbook.Unprotect

On Error Resume Next
ActiveWorkbook.PublishObjects.Add _
xlSourceSheet, _
strFileName, _
"Blank Work Request Form ", _
"", _
xlHtmlStatic, _
"", _
""

ActiveWorkbook.PublishObjects.Publish