VBA publishing range as html

[wetech]

I created a simple macro to publish a range of cells to a directory as an htm file. The macro works perfectly when running it directly from the VBA editor.

I created a button to run the macro from the worksheet. The button simply calls the publishing macro. The problem is, the macro doesn't work when called from the button. I get an "Method 'Publish' of object 'PublishObject' failed" error (Error # 1004).

After getting this error, the macro will not work when running directly in the editor again unless I manually republish the range.

Any thoughts? this is driving me nuts. Code below:

Sub Publish_Range()
Dim rngSend As Range
Const htmlFile As String = "c:\temp.htm"

Set rngSend = Worksheets("Carry").Range("B22").CurrentRegion

With ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange, _
Filename:=htmlFile, _
Sheet:="Carry", _
Source:=rngSend.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
End sub

 

[KLin]

1. open the VBA editor
2. right click the vbaproject name and click insert module
3. copy and paste code into a new public sub within new module
4. in the click event of the button put in a line of code that calls the public sub (ie Call Publish_Range)
5. save changes
6. test button again

Public Sub Publish_Range()
Dim rngSend As Range
Const htmlFile As String = "c:\temp.htm"

Set rngSend = Worksheets("Carry").Range("B22").CurrentRegion

With ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange, _
Filename:=htmlFile, _
Sheet:="Carry", _
Source:=rngSend.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
End sub

 

[wetech]

tried something similar already... doesn't work.

I've tried calling the sub from the button and pasting the code directly in the button_click sub. same results each time.

I can run the code directly from the editor no problem, but get errors when clicking the button.

 

[wetech]

figured it out. After reading through the help files in excel, it turns out that certain VBA methods are disabled when an ActiveX control is active. The workaround is to activate something else before doing what you need to. So I threw a Range("A1").activate line in before calling the publish macro, and it works fine.

very annoying.