Anyone ever use %Activate with Excel?

Caché

I used the Activate wizard to create a package for Excel (Activate.Excel). There is a class in there that seems to be what we need to open a file:

Activate.Excel.Workbooks.

 

There is a method that  has an Open() command. I tried  using it but getting an invalid pointer error.

 

The  parameters are as follows:

Method Open(Filename As %String, UpdateLinks As %Activate.Variant, ReadOnly As %Activate.Variant, Format As %Activate.Variant, Password As %Activate.Variant, WriteResPassword As %Activate.Variant, IgnoreReadOnlyRecmmndd As %Activate.Variant, Origin As %Activate.Variant, Delimiter As %Activate.Variant, Editable As %Activate.Variant, Notify As %Activate.Variant, Converter As %Activate.Variant, AddToMru As %Activate.Variant, Local As %Activate.Variant, CorruptLoad As %Activate.Variant) As Activate.Excel.uWorkbook [ Final, Not Internal, Language = cache ]

 

I put in the Filename parameter and created a %Activate.Variant var with NULL values and passed that as the remaining parameters. I also just tried just the Filename parameter since according to Microsoft the rest of the parameters is optional / not required.

 

Here is a link from Microsoft for this method:

https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open

 

Has anyone ever used this before or any advice on how to use a %Activate type ( this is my first time).

 

Thank you in advance for y our replies.
 

Answers

If you need to consume data from Excel check this Apache POI integration.

No, we are not trying to consume data from Excel. We created an Excel file with one of our routines. It is a nicely formatted Excel file in Excel XML format. We have a group that is requesting that we send that data over in XLSX format. We have figured out a way to use Excel COM operations through Powershell to get this to work. We would like to figure out how to do this in Cache using the Activate classes. There's not that much documentation out there on how to do this.

Apache POI can write to Excel files, you can extend the current integration if you want.

Thanks! I'll go to the Apache page and read up on this.

Do you know if this java library will be able to read an Excel XML file and convert to Excel XLSX file while retaining all of the formatting that we created in the Excel XML file?

Yes it can, check this post.

But for conversion I would recommend LibreOffice. Here's how it can be used from Cache/Ensemble/InterSystems IRIS.

We have used this method in LibreOffice to convert but it ignores some of the formatting (cell colors, etc) that we have in the Excel XML.

Well, maybe Apache POI would be better.

In my opinion the best approach to MS Office docs is to try things with the real-life samples. Far to many combinations to try to predict anything.

I will do more research with POI. It looks like something we could use here at work. But for now, I figured out how to use %Activate with Excel to do what we need to do.

Thanks for your help Eduard!

Comments

As per Eduard, this is how we were able to use %Activate with Excel.

1. Go to the Activate wizard and look for the Excel object. In our case it was Microsoft Excel 16.0 object library. This will create the appropriate package/classes.

2. In our case, the functionality we needed was the Save As function of Excel. Through the Microsoft documentation and also trial and error:

Set SourceFile="location of file"

Set DestFile="location of file"

Set App=##class(Activate.Excel.Application).%New() //this will create an Excel application object

Set Workbooks=App.Workbooks.Open(SourceFile) //bring that Excel file into memory

Set XLSXFormat=51 //the format that we need, found some docs //here:  https://www.rondebruin.nl/win/s5/win001.htm

DO Workbooks.SaveAs(DestFile,XLSXFormat)

DO Workbooks.Close()

Understanding the structure of this package now, it looks like we can explore more with other features/functions of Excel.