RSVIEW VBA problem!

K

Thread Starter

KJ Lee

Hello!
I am new to VB. I had to complete a SCADA project use RSVIEW. All SCADA graphics and animating were completed but customer requested to print daily and monthly report .
Since it was not RSVIEW standard function to periodicly print tag value as report. I planned to use VBA to call MS Excel to form the report. This was my first time to use VBA. I found it was some trouble to reach my target by only reading on line help.
I would like to know whether anyone uses VBA in RSVIEW. How could they use VBA to call Excell and put tag values into cells each one hour. I was very appreciated anyone to help me. Some sample code was especially helpful to me.

Thanks

K.J Lee
E-Mail:[email protected]
 
M

Manny Hellstern

My suggestion is that you write VB code within an excel spreadsheet. This code can be run from a CommandButton control that you put on the
spreadsheet. Since you are printing out daily and monthly reports, you obviously have some type of database historian where the data is stored.
Within the VB Code, you connect to this database using an ODBC connection and then create a record set by issuing a query against the database. Once
you have the record set your code will "Do while not eof" to retrieve data and at the same time move the cursor in excel to different cells in order to insert the values from the recordset.

This is a brief synopsis of the activity. I don't think you need to mess with RSView if you are getting historical information. Yes, you do need
RSView, which is a DDE server, if you want to display dynamic real time values within an excel spreadsheet, which is the DDE client.

If I have a chance, I'll try to send you some sample code for this type of application.

Manny Hellstern, P.Eng
Mustang Engineering
Houston, Tx
 
P
dear kj lee,

We are using Rsview with VBA facility. It is possible to down load daily report / monthly report in MS Excel format.

Try it....

Pankaj Dave
 
M
This is very similar to what I was doing when I made a recent post to this list.

You can use RSView to write the required tags to the worksheet. Use derived tags to do this seamlessly, or you can use Event/Macro combinations if you only want to write the tags periodically.

A simple VBA script in the worksheet can be called on an event in RSView.

The 'Event' will be something along the lines of the following;


Enabled: Y
Action: DDEExecute EXCEL|SYSTEM
[RUN("MYSHEET.XLS!SaveSheets", FALSE)]
Expression: ((system\Hour == Shift1Hour) && (system\Minute ==
Shift1Min)) || ((system\Hour == Shift2Hour) && (system\Minute == Shift2Min))

The action runs the VBA script sub routine SaveSheets in the workbook Mysheet.xls, whenever the Expression evaluates to TRUE (Note: it does this once on a false to true transition).

The above solution was used to save a copy of the report to disk at the end of each shift. This was used to replace a similar solution which printed the report to a local printer (the problem was nobody ever checked that there was paper in the printer)..

If you contact me directly I can give you some more specific help.

Regards
Mark Hutton
Software Engineer
Vogal Software Services
Regent House, Welbeck Way, Peterborough. UK PE2 7WH
Tel: +44 (0)1733 370789 Fax: +44 (0)733 370701
[email protected]
 
M
Run the VBA script from an event detector file every hour. Select Excel in the references for VBA.

Sub ExcelReport()
Dim objExcel As Excel.Application
Dim tTag As Tag

Set tTag = gTagDb.GetTag("System\second")
Set objExcel = CreateObject("Excel.Application")

With objExcel
.Application.Visible = False
' open an existing workbook
.Workbooks.Open "C:\book1.xls"
' or create an new workbook
'.Application.Workbooks.Add
.Worksheets("Sheet1").Activate
.Cells(1, 1).Value = tTag.Value
' print it
'.Worksheets("Sheet1").Print
' or save it and print it later
.ActiveWorkbook.SaveAs "C:\report.xls"
.Application.Quit
End With
Set tTag = Nothing
Set objExcel = Nothing
End Sub
 
D

David Kirkpatrick

<P>I would write the VBA code all in RSView if possible, and "push" the data over to excel rather than put the code in excel and "pull" the data from RSView. I would also do this via the Excel object model in vba rather than use DDE. I would set up an event in RSView that gets triggered when you want your report to run, say every morning. This event would call VBAExec to run your VBA subroutine. The Subroutine would
drive excel. Something like this:
<PRE>
Public Sub doexcel()

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim tg As Tag

' Start Excel & minimize it
Set xlApp = CreateObject("Excel.Application")
xlApp.WindowState = xlMinimized

' Open your workbook, and switch to sheet2
Set xlWb = xlApp.Workbooks.Open("c:\temp\foo.xls")
Set xlWs = xlWb.Worksheets("sheet2")
xlWs.Activate

' Write a tag value to cell b2
Set tg = gTagDb("SYSTEM\DATEANDTIMESTRING")
xlWs.Cells(2, 2) = "HELLO WORLD. The RSView time is: " & tg.Value

' Print, Save & quit
xlWs.PrintOut
xlWb.Save
xlApp.Quit
Set xlApp = Nothing
End Sub
</PRE>
<P> Good luck,<BR>
David Kirkpatrick<BR>
Animas Automation & Controls.<BR>
[email protected]
 
D

Denis Lefebvre

As far as I remember, there are previous and next pages (or windows) commands, but they do not work the way you would expect. I have not checked
the new features of RSView, but eight months ago, the most effective way to do it was to use a custom macro to display all pages. Everytime a new page is displayed, its name would be stored in a stack, from where it could be recalled by a page backward macro. If you use the multiuser RAD
environment, you will have to create and maintain one stack per user.

Denis Lefebvre
 
The Problem with this Code is everytime you open the excel sheet Its always At Cell Location b2
i need this code to increment the Cell Locations
 
Top