Question:
How can I read and/or write in Excel files with the Elipse solutions (Elipse E3/Power)?
Solution:
Microsoft Excel is a spreadsheet developed by Microsoft for platforms such as Windows, macOS, Android, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA programming). Excel is part of the Microsoft Office suite of software.
As previously seen in the article KB-12658: Reading/Writing TXT files with E3, you can read/write data in a TXT file by using the methods in the FileSystemObject object. And in this present article, we will use a similar logic when proposing a way to read and/or write Excel files via Elipse E3/Power. But in this case, however, we will change system objects and opt for one used specific for manipulating Excel files. The chosen object for handling this type of file is EXCEL.APPLICATION, and the solution will explore its main methods and properties, as well as its internal objects.
Some examples are:
** Script for creating excel file**
Dim objExcel,objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.add
objWorkBook.SaveAs "C:\arq3.xlsx"
objWorkBook.Close
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
** Script for creating new spreadsheet in file**
Dim objExcel,objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.open ("C:\arq3.xlsx")
objWorkBook.Sheets.Add
** Script for adding information to file**
Dim objExcel,objWorkBook,sheet
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.open ("C:\arq3.xlsx")
Set sheet = objWorkBook.Sheets ("Planilha1")
sheet.Cells(1,1) = "Nome1"
** Script for deleting spreadsheet from file**
Dim objExcel,objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.open ("C:\arq3.xlsx")
objWorkBook.Sheets("Planilha1").delete
** Script for deleting excel file**
Set obj = CreateObject("Scripting.FileSystemObject")
obj.DeleteFile("C:\arq3.xlsx")
By following the examples above, you will be able to manipulate Excel files with the Elipse platforms (Elipse E3 and Elipse Power).
NOTE: In order to run Office apps (Excel and others) in the system account (SYSTEM), and run the script in server objects, you will need to create a folder called Desktop in the following directories:
C:\Windows\system32\config\systemprofile\
C:\Windows\SysWOW64\config\systemprofile\
The demo application attached to this article was developed with Elipse E3 version 5.1 build 186.
Related articles:
Attachments:
