Promotic
WikipediaLinkedInYoutubeTwitterFacebook

MS Excel usage examples

Excel database in the PmaDatabase object

- When creating the object from Preconfiguration "PmaDatabase - Database", select: Excel.
- In a newly created PmaDatabase object, on the "Database" tab, set the configurators: Technology, Database (file, folder, data source), Table (file), etc.

Excel database in the PmaAdo object

- When creating the object from Preconfiguration "PmaAdo - ADO database", select the database type: Excel and in the "Database connection parameters" configurator select: MsExcel 2007,2011 - Provider Microsoft or MsExcel 2003 - Provider Jet.4.
- In the "Database connection parameters" configurator it is necessary to modify the file name and path to database file.
The PmaAdo object is created, containing the DtiOper method on the "Methods" tab. This method represents the basic inteface for working with database tables and can be used, for example, by the Preconfiguration "Panel with editable table viewer of data source" viewer.
 
The interface uses the ADO technology that can be extended by additional functionality:
Example:
Creates new table data with columns nTime (time), 'flags' (integer) and 'value1' (real number).
JavaScriptVBScriptSelect and copy to clipboard

pMe.DbExecute("", "CREATE TABLE data (nTime date, flags integer, value1 double)", "");
 
Creating a new table (CREATE TABLE) in the Excel database means creating a new Sheet.
Removing a table (DROP TABLE) however does not cause sheet removal, but only empties its content.

In the script by using the GetObject and CreateObject methods

- The GetObject method can be used for opening and getting reference to MsExcel application. Then can be used the internal implementation of VBA language (Visual Basic For Aplications) that is a part of the Microsoft Office for managing excel sheets and data content.
- One possible disadvantage could be the fact that reading or writing large ammounts of data can be very time consuming.
Example1:
Open the file Test.xls, with included 'Sheet1'. Read the value from the "B3" cell and assign it into the a variable. Write the value of 3.14 into the "D7" cell. Display the MsExcel application. Save and close the application.
JavaScriptVBScriptSelect and copy to clipboard

var oWbs, oExcelApp, oSheet, a;
oWbs = Pm.AxGetObject("new", "Excel.Application");
oExcelApp = oWbs.WorkBooks.Open("c:\\Data\\Test.xls", 3, false, 4);
oSheet = oExcelApp.Sheets("Sheet1");
a = oSheet.Cells(3, 2).Value;
oSheet.Cells(7, 4).Value = 3.14;
oExcelApp.Parent.Windows(1).Visible = true;
oExcelApp.Save();
oExcelApp.Application.Quit();
 
Example2:

Creates new EXCEL file.

Display the MsExcel application.

Creates sheets (according to default Excel settings).

Creates a reference to the first sheet.

Write values into the first sheet, save and close the application.

It is possible to export the content of the file into pdf format (since version Microsoft Office 2007).

JavaScriptVBScriptSelect and copy to clipboard

var oExcelApp, oWorkbook, oSheet;
oExcelApp = Pm.AxGetObject("new", "Excel.Application");
oExcelApp.Visible = true;
oWorkbook = oExcelApp.WorkBooks.Add;
oSheet = oExcelApp.Sheets(1);

oSheet.Cells(1, 1).Value = oWorkbook.Sheets.Count;
oSheet.Cells(1, 2).Value = 34;
oSheet.Cells(2, 1).Value = Pm.Time;
oSheet.Cells(2, 1).NumberFormat = "d/m/yyyy h:mm:ss";

oExcelApp.ExportAsFixedFormat(0, "C:\\Data\\Test\\Report.pdf", , , , , true);  //Export to pdf format file
oSheet.SaveAs("C:\\Data\\Test.xls");
oExcelApp.Quit();

History:
Pm8.02.00: Created
PROMOTIC 9.0.12 SCADA system documentation - MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice