[Inventor 2022.1.1] iLogic excel writes end up in the wrong column
Hi @mrc4nl. You're right. I am using the same 2022.1.1 version and I just tried your code on the provided Excel file. It can read from the file, but not write to it using those GoExcel tools. I even saved the file locally, commented out all lines but the file path and the one GoExcel line that is attempting to write data to a cell, including the file name, sheet name, and cell address typed in plainly. It just won't write to any cell in any Excel file (I also tested on other local Excel files, with both .xls & .xlsx file extensions, with same results). It will read from a cell that has a value in it, but won't write anything back to that same cell when you reverse the line of code. Seems like a bug to me.
Wesley Crihfield
(Not an Autodesk Employee)
If you write the column letters in Capital, it will work (tested in R0222.1.1)
Row_e = "E" + CStr(startrow) Row_c = "C" + CStr(startrow) Row_h = "H" + CStr(startrow)
Changing the letters in the cell address to upper case was the first thing I did yesterday before posting, because it was the most obvious thing to try. But that didn't fix the issue for me.
What I realized later though after more testing is that after my attempt to write a new value to a cell in Excel, it wasn't visually changing the value in that cell in Excel, but I was able to now read the value I gave it from that cell in later lines of code, even though that was not the value currently showing in the cell on the Excel sheet. Very odd. Almost seems like there needed to be a screen update within the Excel application for the new value to show up correctly. But that wasn't it either. I think the value I tried to send to that cell was just being held in memory somehow, then retrieved again by the following line of code to retrieve the value from that cell, without that value actually changing anything in Excel.
The only way I could get the new value I send to the cell to stay and persist was to leave that file and Excel closed, then run the rule to send the new value to the cell, which includes the GoExcel.Save line. Then when I open the file, it's finally showing the new value. However, if I have Inventor open in my left hand screen and that Excel sheet open in my right hand screen, and I run the same rule (with the save line commented out this time), it is not changing the value of that cell. I have tried clicking on that cell, and it still shows the same original value within. Nothing has changed. Just using the most simple lines of iLogic snippet codes, like:
oXLFile = "C:\Temp\Test1.xlsx"
oSheet = "Sheet1"
GoExcel.CellValue(oXLFile, oSheet, "A2") = "Test Value"This is just another reason why I usually prefer to avoid most of the common iLogic snippet codes in my work related solutions. I usually prefer to go the longer, more proper route of referencing the Excel object library, and using actual Excel objects the way they were meant to be used, by vb.net (or in a few cases by VBA).
Wesley Crihfield
(Not an Autodesk Employee)
I only saw that error under when certain conditions were met while the file was open, like if the file is open and I use the line of code to open that file, or similar. Certain ways I format the code doesn't cause that error to pop-up for me. I know the file, sheet, nor the cells are write protected because I can still write to them manually, and I can still write to them using the traditional methods in vb.net (without iLogic's GoExcel Interface).
I prepared an example of two different iLogic rules that are designed to do the same exact simple task of writing column titles to the first 3 cells along the top left of the sheet. One uses iLogic's GoExcel Interface, and the tools it has available. The other example uses the traditional vb.net route of adding a reference to the Excel object library, then using Excel's own objects & methods to interact with it. Both versions are using the same exact Excel file, same sheet, and same cells, and trying to write the same data to those cells. The worksheet is visibly open while running both versions. The GoExcel version doesn't write the data, but the other version does write the data. This tells me there must be something else going on. I have the Excel Engine in the iLogic Configuration settings set to 'COM', as it should be, because I have Excel installed and generally prefer to use it rather than the 'internal' tool.
Example 1 (GoExcel)
oXLFile = "C:\Temp\Test1.xlsx"
oSheet = "Sheet1"
GoExcel.Open(oXLFile, oSheet)
GoExcel.DisplayAlerts = True
GoExcel.CellValue("A1") = "Column 1"
GoExcel.CellValue("B1") = "Column 2"
GoExcel.CellValue("C1") = "Column 3"
'GoExcel.CellValue(oXLFile, oSheet, "A1") = "Column 1"
'GoExcel.CellValue(oXLFile, oSheet, "B1") = "Column 2"
'GoExcel.CellValue(oXLFile, oSheet, "C1") = "Column 3"
'GoExcel.Save
'GoExcel.Close
Example 2 (Excel Object Library + vb.net)
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
oXLFile = "C:\Temp\Test1.xlsx"
oSheet = "Sheet1"
If Not System.IO.File.Exists(oXLFile) Then Exit Sub
Dim oExcel As Microsoft.Office.Interop.Excel.Application
oExcel = GetExcel
If IsNothing(oExcel) Then Exit Sub
oExcel.DisplayAlerts = False
oExcel.Visible = True
'Get/Open the Workbook (file) to work with
Dim oWB As Workbook = oExcel.Workbooks.Open(oXLFile)
'Dim oWB As Workbook = oExcel.Workbooks.Add()
'Get the Worksheet (sheet) to work with
Dim oWS As Worksheet = oWB.Worksheets.Item("Sheet1")
'Dim oWS As Worksheet = oWB.Worksheets.Add
'Write Values To Cells by their Cell Address
oWS.Range("A1").Value = "Column 1"
oWS.Range("B1").Value = "Column 2"
oWS.Range("C1").Value = "Column 3"
oWS.Columns.AutoFit
'oWB.Save
'oWB.Close
'oExcel.Quit
End Sub
Function GetExcel() As Microsoft.Office.Interop.Excel.Application
Dim oXL As Microsoft.Office.Interop.Excel.Application
Try
'try to find an already running instance of the Excel Application
oXL = GetObject(, "Excel.Application")
Catch
'it wasn't found open, so create an instance of it (start the application)
oXL = CreateObject("Excel.Application")
'oXL = New Microsoft.Office.Interop.Excel.Application
Catch
MsgBox("Failed to Get/Create an instance of the Excel Application. Exiting.", , "")
Exit Function
End Try
Return oXL
End Function
Wesley Crihfield
(Not an Autodesk Employee)
