excel macro to cicode?

I would like to have a button on a graphics screen when pushed do the following in cicode or vba or whatever is best using Citect.

When pushed:
search for a excel file called master.
retrieve and paste part of four different event logs onto 1st sheet.
save information to another worksheet labeled (day1)by using date on master sheet. Example 03/01/2004 to day1, 03/02/04 to day2, worksheets.
When completed, exit out of all four retrieved worksheets.
Exit out of master workseet.
Back to original screen with pushbutton.

Below is an excel macro that does this up to the point of retrieving and saving on the 3rd worksheet. I actually what it saved as described above.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/1/04 by Connie
'
' Keyboard Shortcut: Ctrl+z
'
ChDir "C:\My Documents\excel test"
Workbooks.Open FileName:="C:\My Documents\excel test\Book1.xls"
Range("A19").Select
Selection.Copy
With ActiveWindow
.Top = 116.5
.Left = 118.75
End With
Windows("master.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A4").Select
Workbooks.Open FileName:="C:\My Documents\excel test\Book2.xls"
Range("B1:E11").Select
Application.CutCopyMode = False
Selection.Copy
With ActiveWindow
.Top = 76
.Left = 109
End With
Windows("master.xls").Activate
Range("E1").Select
ActiveSheet.Paste
Range("E4").Select
Sheets("Sheet2").Select
Range("A1:H11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A10").Select
Sheets("master").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A7").Select
Windows("Book2.xls").Activate
With ActiveWindow
.Top = 70
.Left = -20
End With
ActiveWindow.Close
Windows("Book1.xls").Activate
With ActiveWindow
.Top = 104.5
.Left = -0.5
End With
ActiveWindow.Close
Range("B2").Select
End Sub

Why do you want to use Excel? Was not the method I posted for you OK? It can be modified to do almost anything you want.

Citect will communicate with Excel via DDE.

beerchug

Not sure what to write into Citect, thats why I put this with a macro in excel. I thought maybe someone could show me how to do the samething with the files shown using the macro in excel, into "Citect" instead to print yesterdays report. Rich

I can create a button, but do not know what to add as a code.

And if the supervisor wanted a date other then yesterdayl, is it possibel to have a place on the graphic screen where the date wanted is typed in and it finds the saved combined form such as, 02/25/2004, or 02/20/2004 or whatever?

You can set up a "Device" to write to a file at a certain time each day, if you wish. You can then set up a button for, say, 7 days. Each button will then print the file for the appropriate day.

This is something I do regularly with Citect but I cannot send you the whole file so that you can work it out. The file is fairly large.

Citect support may be able to send you more. There are extensive help files in Citect that should help also.

I will see if I can send you some more information but it would be in Citect V5.42. My projects have been upgraded to that version and older versions will not read the file. Please let me know if you have version 5.42 so that you can read a file.

There is also a lot of information in the sample project.

beerchug

Thank you and yes we use Citect V5.42

Give me a bit more time Rich101. I am very busy doing some design work and have not had a chance yet.
banghead

Here is the code from work, although I think I stopped saving it, just retrieves parts of files and puts them in excel. The reason it goes to excel is that it does a lot of calculations there and the form we have to use is in excel. Can't change that. Rich

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/5/2004 by rich'
' Keyboard Shortcut: Ctrl+y
'
ChDir "C:\Citect\Logs"
Workbooks.Open Filename:="C:\Citect\Logs\PLANTA.001"
Range("B1:I25").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Master.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("PLANTA.001").Activate
ActiveWindow.Close
Sheets("R-GEN1").Select
Workbooks.Open Filename:="C:\Citect\Logs\GEN1A.001"
Range("D1:I25").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Master.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Sheets("R-GEN2").Select
Windows("GEN1A.001").Activate
ActiveWindow.Close
Workbooks.Open Filename:="C:\Citect\Logs\GEN2A.001"
Range("D1:I25").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Master.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("GEN2A.001").Activate
ActiveWindow.Close
Sheets("R-GEN3").Select
Workbooks.Open Filename:="C:\Citect\Logs\GEN3A.001"
Range("D1:I25").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Master.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("GEN3A.001").Activate
ActiveWindow.Close
End Sub

what a leaning experience I am having using Citect. But it seems like a great program.

One problem I am still having though is how using dde in excel,. Do I put into excel cells variable tags? I tried using a macro I found through excel in the Citect bin called Ddeformu, but I'm lost in how to use it. Any tips, etc. Thanks Rich

Rich
Sorry I have not been back sooner.

I do not understand why you are making things hard for yourself. I posted a report file format that will extract the data direct from the PLC and send it to a printer.

The report format I posted is triggered from an entry in reports.
Name - DAILYREPORTS
Time - 08:00:00
Period - 24:00:00
Report Format File - DLYRPT
Output Device - Selected Printer.
Prints at 8AM every morning.
Before that there is a "Schedule" function that triggers from reports at 7:59AM. This function turns on a bit, uses the "sleep" function in Cicode to hold the bit on for 2 seconds. All calculations are then performed in the PLCs, 9 of them, and data is placed in registers that will not be overwritten until 7:59AM the next day.

It works like a charm.

You can also set up a button on the screen to print the report at any time of the day if another copy is required, as the data is in PLC registers until the next day.
In the button setup select "Down Left Button"
The command is Report("DAILYREPORTS"); (no spaces).

It really is the simplest way. I never use Excel for reports. I do use it sometimes for reading data but use Omron CX-Server objects to extract the data direct to a spreadsheet. With Citect I go the above way because it is easy and works.

beerchug

The main reason I could not do it that way is that management wanted it in excel on a specific form. And certain information always in the same place on the form each day it print. I really appreciate your help and most of all the time you have taken to answer my questions.

I finally learned to use the ddeformu macro in citect for excel to tie into the variable tags in citect. And wow! I am now able to fill in the excel templates as required. This also alows engineering calculations to be easily done on other sheets in the excel workbook. Again thank you for your time. I'm sure I will have other questions latter and glad I have others to help. Rich