What have you done with Archie's Advanced HMI?

dcooper33 · Mar 25, 2013

the_msp said:
Could you explain how you did this? Can you use Advanced HMI instead of a licensed version of Rslinx such as OEM?

You bet.
Archie and dmroeder were kind enough to help me set this up for the first time.
I have RSLinx OEM, but I actually needed to open Excel in the background, log PLC data, and save the spreadsheet, all from a single button-click without further user interaction.
This can be done without RSLinx (or Logix) altogether, all you need is an established connection to the PLC you want to pull data from.

The following code can be used as the sub to handle a vb button click. It will open an existing spreadsheet, pull the values in N7:0-99 (SLC or Micrologix processor in the example), and load them in a column in the spreadsheet.
You can modify this easily to apply to any data table and length of read. To use for another PLC, just substitute the appropriate driver in place of "EthernetIPforSLCMicroComm1".

(Before AdvancedHMI can interact with Excel, you have to add a reference to the project. From AdvancedHMI:
Project --> Add Reference.
Click on COM tab and scroll down to Microsoft Excel xx.x Object Library and click OK)

Code:

 Private Sub Log_PLC_Data_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Log_PLC_Data_Button.Click
        Dim PLC_Data_xlapp As Microsoft.Office.Interop.Excel.Application
        Dim PLC_Data_wb As Microsoft.Office.Interop.Excel.Workbook
        Dim PLC_Data_ws As Microsoft.Office.Interop.Excel.Worksheet
        Dim PLC_Data_XL_Open As Boolean = False
        If System.IO.File.Exists("C:\My Spreadsheet Folder\My PLC Log.xlsx") Then
            PLC_Data_xlapp = New Microsoft.Office.Interop.Excel.Application
            PLC_Data_wb = PLC_Data_xlapp.Workbooks.Open("C:\My Spreadsheet Folder\My PLC Log.xlsx")
            PLC_Data_ws = PLC_Data_wb.Worksheets(1)
            PLC_Data_XL_Open = True
        Else
            MsgBox("The spreadsheet you are wanting to load does not exist at ""C:\My Spreadsheet Folder\My PLC Log.xlsx"".  Make sure spreadsheet is in correct location")
            PLC_Data_XL_Open = False
        End If
        If PLC_Data_XL_Open = True Then
            Dim data as Integer
 
            For i As Integer = 0 To 99  'Read the values in N7:0-99 and load into Column in Excel
                data = EthernetIPforPLCSLCMicroComm1.ReadAny("N7:" & i)
                PLC_Data_ws.Cells(4 + i, 2).Value = data
            Next
            PLC_Data_ws.Range("A2").Value = Now
            PLC_Data_wb.SaveAs("C:\My Spreadsheet Folder\My PLC Log.xlsx")
            PLC_Data_wb.Close()      'Close the workbook
            PLC_Data_xlapp.Quit()    'Close Excel
 
            PLC_Data_ws = Nothing    'Release the worksheet from memory
            PLC_Data_wb = Nothing    'Release the workbook from memory
            PLC_Data_xlapp = Nothing 'Release Excel from memory
            PLC_Data_XL_Open = False
        End If
    End Sub

Archie · Mar 26, 2013

gbradley said:
Please do.
I would love to see an example of collecting data using Excel.
Thanks

There is a fairly easy to use library on Codeplex called EP Plus for creating Excel spreadsheets in VB.NET without Excel on the machine. It's much easier to use than Excel Automation. The only drawback is that you cannot have the file open in Excel while the library tries to open it. I also found that if you do not close the file in your VB, it will corrupt the file. I used it on a project where the customer created a template in Excel and wanted data to be filled in. So the code basically opens the template, fills in the data, and does a "Save As".

You can download it here:

http://epplus.codeplex.com/

the_msp · Mar 30, 2013

bmacattack33 said:
You bet.
Archie and dmroeder were kind enough to help me set this up for the first time.
I have RSLinx OEM, but I actually needed to open Excel in the background, log PLC data, and save the spreadsheet, all from a single button-click without further user interaction.
This can be done without RSLinx (or Logix) altogether, all you need is an established connection to the PLC you want to pull data from.

The following code can be used as the sub to handle a vb button click. It will open an existing spreadsheet, pull the values in N7:0-99 (SLC or Micrologix processor in the example), and load them in a column in the spreadsheet.
You can modify this easily to apply to any data table and length of read. To use for another PLC, just substitute the appropriate driver in place of "EthernetIPforSLCMicroComm1".

(Before AdvancedHMI can interact with Excel, you have to add a reference to the project. From AdvancedHMI:
Project --> Add Reference.
Click on COM tab and scroll down to Microsoft Excel xx.x Object Library and click OK)

Code:

code...

Thanks for sharing. Tried this out and am getting 4 errors, all " 'Microsoft.Office.Interop.Excel.Application' is not defined."

Any ideas where I'm going wrong?

dcooper33 · Mar 30, 2013

the_msp said:
Thanks for sharing. Tried this out and am getting 4 errors, all " 'Microsoft.Office.Interop.Excel.Application' is not defined."

Any ideas where I'm going wrong?

Yes, it sounds like you did not create a Project Reference to Excel in your VB like I described in previous post.

See the attached screenshots, which illustrate the compile errors if no reference exists, the steps to adding an Excel Reference, then the same application w/o compile errors after reference is added.

Following this process should get you up and running.

the_msp · Mar 31, 2013

No, I added Excel 10.0 before I added the code and still got the errors. It wouldn't let me add excel 5.0 for some reason.

I see you are using VBE 2010 and I am using 2012, could there be some difference in the two versions?

dcooper33 · Mar 31, 2013

Yes I am using vb 2010. I suppose it is possible that the syntax for Excel interaction is different between versions of VB studio.
I will try installing VB 2012 on my PC to see if I can replicate the error.

dcooper33 · Mar 31, 2013

@the_msp

After installing VB2012 and adding the previous code, I got the same errors you had after attempting to add a Project ref to Excel. I then went back to the project ref dialog and realized that the checkbox for the selected reference does not automatically check when you highlight a reference. After checking the Excel reference and clicking "OK" the code compiled without errors.

$C:\Users\Dustin\Pictures\ScreenShots\VB_2012_Add_Excel_Ref.png$

EDIT: I see that you had the reference checked in the dialog box in your screen shot. I'm using Excel 2010 and Excel 14.0 library object. Is Excel 10.0 a library to Excel 2007? This seems to be the only difference between our two example programs.
You might try playing with syntax of "Microsoft.Office.Interop.Application". If you start with "Microsoft.Office..." Then VB should give you a drop-down list of available components. I'm guessing that the "Interop" could be the undefined part in your application.

the_msp · Mar 31, 2013

Thanks for trying. According to Microsoft website, "If you are automating Microsoft Excel 2002, the type library appears as Microsoft Excel 10.0 Object Library in the References list" - which would be correct for me as I am using Office 2002.

the_msp · Apr 1, 2013

Success! I tried it out in work earlier using VSE 2010 and Excel 2007 and got it to compile with no errors. So back home tonight and I installed office 2010 on my desktop PC, which then gave me the excel 14.0 object library. Thanks again for helping me out.

Now the next question, is there anyway to have this log data on event? Such as a PLC timer being done, or the PC's system clock?

dmroeder · Apr 1, 2013

In the Toolbox, under components, you will find a Timer object. Add one to your form and set the interval (in milliseconds) to how often you want to record your data.

If you double click on the timer that was added to your form, it will open it's "tick" event. This is triggered every time your timer reaches it's interval. The code is executed and the timer starts over. You will need your code that reads/writes to excel in the tick event.

I'd recommend opening the spreadsheet in your button click event (like the example) and then start your timer (timer1.start) upon success of opening the spreadsheet. You don't want the timer tick to open it every time, you just want to open it once.

Then you would want the timer tick to write the data to the spreadsheet. You are going to probably need a pointer in order to shift cells (so you don't keep writing over the same cells). You will also probably need to declare your spreadsheet globally (outside of your button event) so that the timer tick can access the spreadsheet too.

Finally you will want a button to stop logging and close the spreadsheet. If you just close your program without first closing the spreadsheet, it will leave an instance of excel running.

If you get stuck on any of that, you can PM me your email address and I'll help you out with the specifics. That way we don't get this thread too cluttered.

gbradley · Apr 8, 2013

OK, This Video was Golden. Advanced HMI - The Free HMI / SCADA Development Package (YouTube)
Without it, I don't think that I would have been able to get Advanced HMI up and running.
Is there anything similar on capturing data to an Excel file?

What have you done with Archie's Advanced HMI?

dcooper33

Lifetime Supporting Member + Moderator

Archie

Member

the_msp

Lifetime Supporting Member

dcooper33

Lifetime Supporting Member + Moderator

the_msp

Lifetime Supporting Member

dcooper33

Lifetime Supporting Member + Moderator

dcooper33

Lifetime Supporting Member + Moderator

the_msp

Lifetime Supporting Member

the_msp

Lifetime Supporting Member

dmroeder

Lifetime Supporting Member

gbradley

Lifetime Supporting Member

Similar Topics