Publishing Excel as Webpage

Question

I am trying to save excel as a webpage using VB Script. The problem I am facing is the sheet names in excel (E.g. Sheet1) also gets saved in webpage. I don't want the sheet name to be present in webpage.

One solution that I found was that if the excel is published as webpage (instead of saving as webpage) then it will not contain the sheet names. But I am facing issues while I try it using VB Script. Below is the code:

Dim objApp, objBook, objRange, objSheet
Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
Set objBook = objApp.Workbooks.Open("C:\Dashboard\excel_files\Account Executive.xlsx")
Set objSheet = objBook.worksheets("Account Executive")
Set objRange = objSheet.Range("C5:I30")

With objBook.PublishObjects.Add(xlSourceRange, "C:\EXCEL_TO_HTML_RANGE.htm", "Account Executive", objRange.Address, xlHtmlStatic, "Title of the page").Publish(True) 
End With


objBook.Close
objApp.Quit
Set objSheet = Nothing
Set objRange = Nothing
Set objApp = Nothing

This code gives a unknown run time error with code 800A03EC. Is there anyway to correct this code or some other way to remove the sheet names from the web page?

Thanks in advance. :)

Thanks to @AxelRichter The final working code with excel 2010:

Dim objApp, objBook, objRange, objSheet

Set objApp = CreateObject("Excel.Application")

objApp.Visible = False

Set objBook = objApp.Workbooks.Open("C:\Dashboard\excel_files\OU Dashboard.xlsx")
Set objSheet = objBook.worksheets("OU Dashboard")
Set objRange = objSheet.Range("C5:J30")

Dim oPOs, oPO
Set oPOs = objBook.PublishObjects
Set oPO = oPOs.Add(4, "C:\Dashboard\EXCEL_TO_HTML_RANGE.htm", "OU Dashboard", "$C$5:$J$30", 0, "TableDIVID", "Title of the page")
oPO.Publish (True)

set oPO = Nothing
set oPOs = Nothing
Set objSheet = Nothing
Set objRange = Nothing


objBook.Close False
set objBook = Nothing 
objApp.Quit
Set objApp = Nothing

Answer 1

There are three problems with your code.

First: The constants xlSourceRange and xlHtmlStatic are probably undefined. So use their values instead. See https://msdn.microsoft.com/en-us/library/office/ff823068.aspx for XlSourceType and XlHTMLType.

Second: The With needs the object returned by the Add and not the response of Publish.

Third: You have messed the DivID with the Title.

Dim objApp, objBook, objRange, objSheet
Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
Set objBook = objApp.Workbooks.Open("C:\Dashboard\excel_files\Account Executive.xlsx")
Set objSheet = objBook.worksheets("Account Executive")
Set objRange = objSheet.Range("C5:I30")

With objBook.PublishObjects.Add(4, "C:\EXCEL_TO_HTML_RANGE.htm", "Account Executive", objRange.Address, 0, "TableDIVID", "Title of the page")
 .Publish(True) 
End With


objBook.Close False
objApp.Quit
Set objSheet = Nothing
Set objRange = Nothing
Set objApp = Nothing

The With .. End With can be replaced with the following code for debugging:

...
Dim oPOs, oPO
Set oPOs = objBook.PublishObjects
Set oPO = oPOs.Add(4, "C:\EXCEL_TO_HTML_RANGE.htm", "Account Executive", "$C$5:$I$30", 0, "TableDIVID", "Title of the page")
oPO.Publish (True)
...