OLE command to save an Excel sheet

[LAC-Bursi]

Hi,

I should write output data on an excel sheet and I would like this sheet is saved automatically with a given name, without it being displayed.

I would like use an OLE command to do it.

Thanks in advance

Fabio

[Nienke Valkhoff]

Hi Fabio,

You can use the ExcelActiveX atom to connect to an existing Excel workbook or to create a new workbook. You can do this using the user interface of the Excel atom or from 4DScript.

If you do it from 4DScript it is possible to keep Excel invisible and write code to save it automatically etc.
To do it from 4DScript,

• Make sure the atom is currently disconnected, using ExcelActiveX_IsConnected(atmYourExcelAtom).
• Use ExcelActiveX_New([c:\temp\YourNewWorkbook.xlsx], 0, atmYourExcelAtom) to create a new file, or ExcelActiveX_Open to connect to an existing one. The 0 means "do not show on screen".
• Use ExcelActiveX_Write to write data to the sheet. You can use <Window | 4DScript Overview> to find out about the parameters.
• Use ExcelActiveX_Save to save the workbook to disk, then ExcelActiveX_Disconnect to break the connection.
• After ExcelActiveX_Disconnect, you can use _Open or _New to create or open another workbook.

Best regards,

Nienke

[LAC-Bursi]

Hi Nienke,
thanks for your reply. I would have further questions.

- atmYourExcelAtom is a pointer? to what?
- If I have an atom with an internal table and I want to write the table content in a workbook created dynamically, how can i do it?

Thanks
Fabio

[Nienke Valkhoff]

Hi Fabio,

atmYourExcelAtom is an atom reference to the ExcelActiveX atom this is indeed like a pointer.

If you use code to set up the connection using an ExcelActiveX atom you can also use code to write data from any table in ED to an Excel workbook. In the function ExcelActiveX_WriteBlock the 7th parameter is a reference to the table. This can be any table in the model. You refer top the table using an atom reference (pointer).

To save the workbook you need ExcelActiveX_SaveWorkbook(...).

I have included a model with a table and an ExcelActiveX atom. The table has aliases that is why I can use RefMytable to refer to this specific table.

A third atom is used to define a function named MyFunction. If you run this function an excel workbook is created, the data in the table is written to it and saved and the connection is closed. The directory C:\tmp\ needs to exists to make it work. You can run the function from the 4DScript interact window.

Best regards,

Nienke

[LAC-Bursi]

Hi Nienke,
you've been really helpful, you've provided me everything I needed!
Thank you for your commitment!
Fabio

[LAC-Bursi]

Hi Nienke,
I have a further question,

if I want to set a style sheet (i.e. cell background color or insert borders) how could I do it?

Using an OLE application I can send VBA commands, but I can also do this using ExcelActiveX?

Thanks
Fabio

[Nienke Valkhoff]

Hi Fabio,

Each ExcelActiveX atom is used to setup a connection to a specific Excel workbook. You can get this reference using the function ExcelActiveX_GetRefWorkbook(e1), where e1 is a reference to your ExcelActiveX atom. You need to store this reference in an ole variable:

Code: Select all

var([oleWorkbook], vbOle, ExcelActiveX_GetRefWorkbook(AtomByName([YourEcelatomName], Model)))

Then you can use the ole command to send vba commands. You can select specific cells:

Code: Select all

Ole(oleWorkbook,[ActiveSheet.Cells(8,12).Select])

or you can select a range:

Code: Select all

Ole(oleWorkbook,[ActiveSheet.Range([B10:E14]).Select])

Note that the global variable oleExcelActiveX_Connection stores the connection to the Excel application. You can for example change the color of the selection with:

Code: Select all

Ole(oleExcelActiveX_Connection, [Selection.Interior.Color], [-4142])

Best regards,

Nienke