Importing variable list from Excel into Codesys

boldrian
2017-03-21
2018-03-21
  • boldrian - 2017-03-21

    Hello,

    i have a list of couple thousand global variables saved in excel and i want to transfer them into Codesys. I can copy/past them but that takes alot of time. Is there a way to transfer those variables faster and more effectively?

    Adrian

     
  • Joan M - 2017-03-21

    Hi Adrian,

    Why selecting all of them in Excel and pasting them into the declaration area is not working for you?

    Just in case you've not noticed that you can switch the declaration area to text mode.

     
  • boldrian - 2017-03-23

    Thank you for responding.

    I can just copy them but the thing is that in the excel files there are alote of variables that need to be placed in different objects. And this transfer from excel to codesys is done frequently in the company in which i am currently working as a student. So basically this task can be done copy/paste style but my task is to make/find a script or a way to do this automatically.

    Adrian

     
  • Joan M - 2017-03-23

    Two possible ways:

    1. in Excel do a macro that generates xml files as the ones that get imported in Codesys. That should not be very difficult, then import them in Codesys.

    2. Codesys 3 gives Phyton scripts... I've never used them, who knows if you can do it from there... it could be a good idea to investigate that.

    Hope this helps...

     
  • hermsen

    hermsen - 2017-03-23

    Whenever you do something more then once in Excel, you should per definition automate it or use another tool
    Have you ever thought of using Notepad++ ? Scripting for generating files and variables can be very well done in Notepad++
    I use that standard in combo with Codesys 3.5. for exactly this reason.
    Plus, Notepad++ can be used as a python editor also.

    good luck!

     
  • bacha.damin - 2017-04-17

    Hello,

    can you tell me how do you resolve this problem (Importing variable list into Codesys) please?

    And thank you in advance

     
  • chwizgl - 2018-03-21

    I think it's none of our business whether excel makes sense or not. There are cases where it does...

    Maybe this could help you:

        # load relevant clr
       import clr
       clr.AddReferenceByName("Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
       clr.AddReference("System")
       # import garbage collector
       from System import GC
       # import .NET library for Excel
       from Microsoft.Office.Interop import Excel
       excel = Excel.ApplicationClass()
       
       excel.Visible = False # makes the Excel application visible to the user
       excel.DisplayAlerts = False
       
       from System.Runtime.InteropServices import Marshal
       #excel = Marshal.GetActiveObject("Excel.Application") # <-- not sure about that one
       # finding a workbook that's already open
       workbooks = [wb for wb in excel.Workbooks if wb.FullName == filepath]
       if workbooks:
           workbook = workbooks[0]
       else:
          workbooks = excel.Workbooks
          workbook = workbooks.Open(filepath,False,True)
       workbook.Saved = True
          
       # select work sheet
       worksheets = workbook.Worksheets(sheetname)
       worksheet = worksheets.Range(worksheets.Cells(1,1),worksheets.Cells(maxRow,maxCol))
       worksheetDict = worksheet.Value2
       
       ...
       
       # Close workbook after readout
       workbook.Close(False)
       excel.Application.Quit()
       excel.Quit()
       Marshal.ReleaseComObject(worksheet)
       Marshal.ReleaseComObject(worksheets)
       Marshal.ReleaseComObject(workbook)
       Marshal.ReleaseComObject(workbooks)
       Marshal.ReleaseComObject(excel)
       workbook = None
       workbooks = None
       worksheet = None
       worksheets = None
       excel = None
       GC.Collect();
       GC.WaitForPendingFinalizers();
       
       
    

    At the end it gets a bit nasty because basically the application closes but the thread in the task manager remains unless you cut all the bindings to the com object . It's a bit a badly documented try and error

     

Log in to post a comment.