Importing variable list from Excel into Codesys

2020-02-27T14:23:48.968000Z

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

Importing variable list from Excel into Codesys

2020-02-27T14:23:47.679000Z

Hello,

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

And thank you in advance

Importing variable list from Excel into Codesys

2020-02-27T14:23:47.273000Z

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!

Importing variable list from Excel into Codesys

2020-02-27T14:23:46.856000Z

Two possible ways:

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.
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...

Importing variable list from Excel into Codesys

2020-02-27T14:23:45.652000Z

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

Importing variable list from Excel into Codesys

2020-02-27T14:23:45.230000Z

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.

Importing variable list from Excel into Codesys

2020-02-27T14:23:44.810000Z

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

Recent posts to Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys

Importing variable list from Excel into Codesys