Excel VBA & Xojo/RS - PublishObjects

Targets Windows
1

Hi everyone,

I’m trying to export a .htm file from Xojo using Excel application and VBA instructions that work fine in Office. But, I’m getting one error when I run the app. The example code is :

-------------------------------------------->
Dim xls As New ExcelApplication
dim n as Integer
dim sFile as String

'vars
sFile=“C:\Report.htm”

'Excel configuration
xls.Visible=True
xls.Workbooks.Add
xls.ActiveWorkbook.Sheets(1).Range(“A1”)=“Hello World”

xls.ActiveWorkbook.PublishObjects.Add(4,sFile,“Sheet1”, “A1”,“xlHtmlStatic”,“Book_Report”,“Report”)
xls.ActiveWorkbook.PublishObjects.Publish (True)
xls.ActiveWorkbook.PublishObjects.AutoRepublish = False

'final message
n=MsgBox( “Process completed successfully”,64)

-------------------------------------------->

The error is OLEException # -2147352567, specifically when I try with the line xls.ActiveWorkbook.PublishObjects.Add()

I have been checking the documentation of MS for these parameters, and certainly the VBA code in office work without problem.

Someone know what I’m passing bad for parameters in Xojo/RS …?

Your help and comments will be greatly appreciated.

2

Hi Elvis,

I found a few changes that makes the code work: 1) the value for xlHtmlStatic = 0, 2) add Publish true at the end of the PublishObjects.Add (otherwise the method is expecting to return an ExcelPublishObject), 3) create a folder in the root directory called ‘Test’ - often saving items in the root directory is unsafe.

Here is example code:

[code] Dim xls As New ExcelApplication
Dim n As Integer
Dim sFile As String

'Variables
sFile=“C:\Test\Report.htm”

'Excel configuration
xls.Visible=True
xls.Workbooks.Add
xls.ActiveWorkbook.Sheets(1).Range(“A1”)=“Hello World”

xls.ActiveWorkbook.PublishObjects.Add(1 ,sFile,“Sheet1”, “A1”, 0, “Book_Report”, “Report”).Publish True
'“xlHtmlStatic” = 0, xlsourceRange = 4, xlSourceSheet = 1

'Final Message
n=MsgBox( “Process completed successfully”,64)
[/code]

Does this work on the computer?

3

Hi Eugene,

Thank you so much for your suggestion. The code work fine, although I can see that syntax in Xojo/RS with VBA instructions is a little different.

I have modified the code with the line that work with the sheet’s name in order to not to have any problem with the PC language.

On other hand, the parameters xlsSourceType that I use is a range of the sheet and not the whole sheet. I changed to the parameter 1 to 4. The following is the link with the xlSourceType enumeration provided by Microsoft if you might be interested:

http://msdn.microsoft.com/en-us/library/office/ff839450(v=office.15).aspx

------------------------------------------------------------------------------------------------------------>

Dim xls As New ExcelApplication
Dim n As Integer
Dim sFile As String

'Variables
sFile=“C:\Test\Report.htm”

'Excel configuration
xls.Visible=True
xls.Workbooks.Add
xls.ActiveWorkbook.Sheets(1).Range(“A1”)=“Hello World”

xls.ActiveWorkbook.PublishObjects.Add(4 ,sFile,xls.ActiveWorkbook.Sheets(1).Name, “A1”, 0, “Book_Report”, “Report”).Publish True

'Final Message
n=MsgBox( “Process completed successfully”,64)

------------------------------------------------------------------------------------------------------------>

Eugene, do you know if is possible with Xojo generate some Add-in for Excel Office …? Iknow that for this purpose I have two options:

1- Visual Studio (VSTO) :
With VS I get assembled files that I must obfuscate in order to protect the code.

2-Office
I can get an add-in directly in Office working with VBA language. But with this option is mandatory apply obfuscation the code to protect the intellectual property if you want sell. Many tools there are for this, but most of these don’t do a good job.

All these comments are because many customers are interested in Office applications and would be interesting to work with my favorite language that is Xojo/RS.

4

[quote=128864:@Elvis Agamez]Eugene, do you know if is possible with Xojo generate some Add-in for Excel Office …? Iknow that for this purpose I have two options:

1- Visual Studio (VSTO) :
With VS I get assembled files that I must obfuscate in order to protect the code.

2-Office
I can get an add-in directly in Office working with VBA language. But with this option is mandatory apply obfuscation the code to protect the intellectual property if you want sell. Many tools there are for this, but most of these don’t do a good job.[/quote]

Elvis,

I do not know of a way to generate an add-in for Excel, other than the MSOfficeAutomation.rbx plugin which comes with Xojo which may have been built with Visual Studio.

It might be possible to call the methods of an Excel Plugin - created with Visual Studio with Xojo. It would take some testing to see if it would work.