Promotic
WikipediaLinkedInYoutubeTwitterFacebook

MS Excel usage examples

Excel database in the PmDatabase object

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

Excel database in the PmAdo object

- When creating the object from Preconfiguration "PmAdo - ADO database", select the database type: Excel and in Database connection parameters configurator select: MsExcel 2007,2011 - Provider Microsoft or MsExcel 2003 - Provider Jet.4.
- In preconfigured Database connection parameters it is necessary to modify the filename and path to database file.
The PmAdois created, containing the DtiOper method on the Methods page. 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:
Create new table data with columns nTime (time), 'flags' (integer) and 'value1' (real number).
pMe.DbExecute "", "CREATE TABLE data (nTime date, flags integer, value1 double)", ""
 
Creating a new table (CREATE TABLE) in excel means creating a new Sheet.
Removing a table (DROP TABLE) however does not cause sheet removal, but only empties its content.

From the script by using the GetObject and CreateObject methods

- The GetObject method can be used for opening and getting reference to MsExcel application. Then it is possible to use the internal implementation of VBA language (Visual Basic For Aplications) that is integrated into 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 to the variable a. Write the value of 3,14 into the "D7" cell. Display the excel application. Save and close the application.
Dim oExcelApp, oSheet, a
Set oExcelApp = GetObject("c:\Data\Test.xls")
Set oSheet = oExcelApp.Sheets("Sheet1")
a = oSheet.Cells(3,2)
oSheet.Cells(7,4) = 3.14
oExcelApp.Parent.Windows(1).Visible = true
oExcelApp.Save
oExcelApp.Application.Quit
 
Example2:
Create new EXCEL file. Display the excel application. Create sheets (according to default Excel settings). Create a reference to the first sheet. Write values into the first sheet, save and close the application. Since version Microsoft Office 2007 it is possible to export the content of the file into pdf format.
Dim oExcelApp, oWorkbook, oSheet
Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Visible=true
Set oWorkbook = oExcelApp.WorkBooks.Add
Set oSheet = oExcelApp.Sheets(1)
oSheet.Cells(1,1) = oWorkbook.Sheets.Count
oSheet.Cells(1,2) = 34
oSheet.Cells(2,1) = Now
'oExcelApp.ExportAsFixedFormat 0, "C:\Data\Test\Report.pdf", , , , , , TrueNow 'Export to pdf format file
oSheet.SaveAs "C:\Data\Test.xls"
oExcelApp.Quit

Additional options of VBA language usage in MsExcel

Possibility to launch MsExcel containing Macro(s).
 
Example:
launch the application "Test.xls" containig the "Macro1" macro
Dim oExcelApp
Set oExcelApp = GetObject("c:\Data\Test.xls")
oExcelApp.Application.Run "Test.xls" & "!Macro1"
 
Based on the OS Windows security settings the MsExcel macro launching is not allowed by default. If the files containing macros are to be launched, then it must be enabled in Microsoft Excel settings.
PROMOTIC 8.3.22 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