Error when using an array for PublishObjects.Add

This is probably something basic I'm overlooking, but I'm in a hurry!

I am trying to create HTML files (via macro) from Excel worksheets and would love to use the code below (from  I keep getting an error (Runtime Error '1004', Application-Defined or Object-Defined Error).  Is it something simple I'm overlooking?  Does 'sName' need to be declared as a particular variable?


Public Sub CommandButton2_Click()

sName = Array("Executive Summary", "Electric")
For i = LBound(sName) To UBound(sName)
ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:="C:\" & sName(i) & ".htm", Sheet:=sName(i), HtmlType:=xlHtmlCalc).Publish
Next i

End Sub

Who is Participating?
anvConnect With a Mentor Commented:
hi DanRollins

I feel The Points Shud awarded to me...
Hope u r using above code in Excel VBA
moreover can u define the exact line where u r getting this error??
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

rmrqAuthor Commented:
Yes, using VBA 6.0, and the error is occurring on the following line:
ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:="C:\" & sName(i) & ".htm", Sheet:=sName(i),HtmlType:=xlHtmlCalc).Publish

Any ideas?
please check if u have given valid references...

'coz i'm not getting any error on my pc..when used the above statement
..ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:="C:\" & sName(i) & ".htm", Sheet:=sName(i), HtmlType:=xlHtmlCalc).Publish

also check if Sheet SName(i) exists or not..
rmrqAuthor Commented:
The two sheets named in the sName(i) array do exist.  As far as references, I have no idea what I may be missing.... any ideas?  
rmrqAuthor Commented:
It must be a reference, because the code itself seems to work for many other people.....
rmrqAuthor Commented:
The question was never answered, but Anv can have the points for attempting to.  I would delete it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.