OLE command to save an Excel sheet
OLE command to save an Excel sheet
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
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
- Posts: 20
- Joined: Tuesday 11 January, 2011 - 09:23
Re: OLE command to save an Excel sheet
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,
Nienke
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.
Nienke
Re: OLE command to save an Excel sheet
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
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
- Posts: 20
- Joined: Tuesday 11 January, 2011 - 09:23
Re: OLE command to save an Excel sheet
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
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
- Attachments
-
- ExcelActiveX_withCode.mod
- (5.11 KiB) Downloaded 309 times
Re: OLE command to save an Excel sheet
Hi Nienke,
you've been really helpful, you've provided me everything I needed!
Thank you for your commitment!
Fabio
you've been really helpful, you've provided me everything I needed!
Thank you for your commitment!
Fabio
Re: OLE command to save an Excel sheet
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
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
- Posts: 20
- Joined: Tuesday 11 January, 2011 - 09:23
Re: OLE command to save an Excel sheet
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:
Then you can use the ole command to send vba commands. You can select specific cells:
or you can select a range:
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:
Best regards,
Nienke
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)))
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])
Nienke