New post
Question
· Oct 7, 2019

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.
 

Discussion (10)0
Log in or sign up to continue
Roberto Cahanap · Oct 7, 2019

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.

0 0
Roberto Cahanap · Oct 9, 2019

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.

0 0