Using Excel PublishObjects with VBScript

Question

Nevermind, I figured it out. Leaving this here in case anyone else is having this issue and searches for it. Basically, the issue is that VBScript wanted a true parameter list and didn't like that I was trying to set the values based on the name of the param like it was in VBA:

objWorkbook.PublishObjects.Add(4,"F:\METRICS_TEAM\OSO\New OSO\Net OSO Acceptance Email.html","Destination",objWorkbook.Sheets("Destination").Range("B1:R29").Address,0).Publish (True)

works fine. See below for details on the problem.

I am trying to move some code over from VBA to VBScript and am having some trouble with the PublishObjects Object when trying to export an Excel range to HTML. Here is the working code in VBA:

With ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange, _
    fileName:="F:\METRICS_TEAM\OSO\New OSO\Net OSO Acceptance Email.html", _
    Sheet:="Destination", _
    Source:=Sheet3.Range("B1:R29").Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
End With

And here is the code I am having issues with in VBScript:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("F:\METRICS_TEAM\OSO\New OSO\Net OSO Acceptance Email.xlsm")

With objWorkbook.PublishObjects.Add(SourceType:=4, _
    Filename:="F:\METRICS_TEAM\OSO\New OSO\Net OSO Acceptance Email.html", _
    Sheet:="Destination", _
    Source:=objWorkbook.Sheets("Destination").Range("B1:R29").Address, _
    HtmlType:=0)
    .Publish (True)
End With

When I run this in VBScript I get a compilation error saying it is missing a closing parenthesis right before the first line continuation... it doesn't seem to like the arguments in the .add method for some reason. I tried removing the with block and the line continuations and I get the same error.

Any help would be much appreciated!

Thanks!

Aditya Reddy Aditya Reddy · Accepted Answer · 2015-03-27T11:53:31

Please use this with statement.

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, "D:\EXCEL_TO_HTML_RANGE.htm", "Sheet1", _ Range("B1:R29").Address, xlHtmlStatic, "Title of the page") .Publish (True) End With

Using Excel PublishObjects with VBScript

1 Answers