PDA

View Full Version : Using Excel for data viewing


Jmolero
10-05-2004, 07:28 PM
Hi all, I need to display tons of data (between 20000-30000 rows with up to 35 columns) coming out of the historical database using sort of grid. First, I used the MSFlexgrid control but it is quite slow (about 2000 rows a minute), later I tried an Excel 2003 macro to connect to the Cimplicity HMI PE 5.5 database (MSDE) using ODBC and fetch the data; certainly, Excel was pretty fast showing and formating the data (hundred times faster displaying 10000 rows of data). As a result of this, I want to use Excel (if possible) as my "grid control" within CimView screens to display and save the data. Have anybody tried some like this? Is possible to control an embedded Excel worksheet? Any ideas?

rjb1
10-07-2004, 09:08 AM
An Excel spreadsheet can be opened in CimView by using Tools|Ole Object to place a Microsoft Web Browser control named Browser1, Object type = Shell.Explorer.2 (PE 6.0). The open workbook can be controlled from a CimView script by using the GetObject function to obtain a reference to Excel. GetObject may not work if there is more than one instance of Excel.

Sub OnScreenOpen()

Dim objExcel As Object
Dim objBrowser As Object

'get reference to browser object
Set objBrowser = CimGetScreen.Object.Objects.Item("Browser1").OleObject

'open spreadsheet file in browser
objBrowser.Navigate "c:\cimplicity\hmi\projects\MyProject\MyWorkbook.xl s"

'wait for Excel object to be created and workbook file to load
Sleep 1000

'get reference to existing instance of Excel
Set objExcel = GetObject (, "Excel.Application")

'use Excel Automation
'change cell A1 to green
objExcel.Windows("MyWorkbook.xls").Activate
objExcel.Sheets("Sheet1").Select
objExcel.Range("A1").Select
objExcel.Selection.Interior.ColorIndex = 4

'refresh an external data query
objExcel.Selection.QueryTable.Refresh BackgroundQuery:=False

End Sub

Excel Automation in the WorkBook_Open event can also be used to control the workbook or sheet. If the workbook contains macros then Excel 'Macro virus protection' may need to be disabled to prevent the Enable/Disable Macro dialog from appearing.

Private Sub Workbook_Open()

Application.DisplayFormulaBar = False

End Sub

Jmolero
10-08-2004, 11:35 PM
Thank you very much for your post rjb1. Now, I tried the OWC instead. Apparently it works fine with Cimplicity (quite stable) and I can display data really fast (5000 rows a second in my system). Spreadsheet does all the query stuff (OLEDB) and data formatting. Really appreciate your reply.