iLogic writing to Excel

[D.Wheeler3GADA]

Is there somewhere I can find good code examples for writing to an Excel spreadsheet?

I have the basics down and I can write to the spreadsheet, but I can't find tutorials on what I would specifically like to do.

In a nutshell I'd like the code to:

1) Query all rows in a specific column for an existing text string (in this case a part number).    'See if the part is already on the spreadsheet

     1A) If text string does not exist, write the part number (iproperty value) to the next available empty cell in the column (working downward).       'Add a part to the spreadsheet that isn't there already

     1B) If the string does exist, write to a specific cell in the same row where the string exists by increasing the number value in that cell by 1.        'Increase quantity of existing parts on the spreadsheet

My issues lie in the coding for the query and utilizing the query results to drive cell placement. I have had success with moving data between the model and the spreadsheet...

Thanks!

[A.Acheson]

Luckily this one has been done before using ilogic goexcel thanks to Curtis. One of my first post to read starting out learning and attempt to implement. 

http://inventortrenches.blogspot.com/2011/04/using-excel-and-ilogic-to-retrieve-part.html?m=1

If you were to use the excel object in VB.Net you would use the equivalent of the range function. Then likely do some internet searching for vba excel macros to do the task you want and then try to convert to vb.net. This post here actually uses a combination of the two ilogic go excel and the excel application object. 

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/create-a-part-number-register-in-exc...

[D.Wheeler3GADA]

@A.Acheson 

      Thank you for the links. They are kind of the opposite of what I want to do, but I am learning from tweaking them. I have almost everything figured out, but I am stuck on the code that would allow me to:

1) Query a column for a matching value and if found, increase the quantity count by 1 in column D of the same row as the matching value.

2) If no match is found in the column query, then add the value to the next blank cell in that column.

I thought this would be fairly straightforward, but I am struggling a bit...

[Curtis_Waguespack]

Hi @D.Wheeler3GADA 

see this example

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

oValueToMatch = 99

'define the file to open
oFile = "C:\Temp\My_Excel_File.xlsx"


'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run invisibly, change to true if you want to run it visibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False

'check for existing file 
If Dir(oFile) <> "" Then
	'workbook exists, open it
	oWB = excelApp.Workbooks.Open(oFile)
	oWS = oWB.Worksheets(1)
Else
	Return 'exit rule
End If

oFound = False

'Loop through the rows in column D
For iRow = 2 To oWS.UsedRange.Rows.Count
	'get value of the cell
	oCellValue = oWS.Cells(iRow, "D").Value
	
	If oCellValue = oValueToMatch Then 
		oWS.Cells(iRow, "D").Value = oCellValue + 1
		oFound = True
	End If

Next iRow

If oFound = False Then
	oWS.Cells(oWS.UsedRange.Rows.Count+1, "D").Value = oValueToMatch
End If

'set all of the columns to autofit
excelApp.Columns.AutoFit
'save the file
oWB.SaveAs(oFile)

'close the workbook and the Excel Application
oWB.Close
excelApp.Quit
excelApp = Nothing     

[D.Wheeler3GADA]

@Curtis_Waguespack ,

        Thank you for the code!. I was able to adjust as necessary to accomplish exactly what I wanted except for 1 single issue I am having:

When the code doesn't see the existing part and adds it to the spreadsheet in the next available blank cell, it always jumps over a ton of empty cells and puts it in at row 509, although there were only 4 rows with data in the spreadsheet...

My code matches your provided code (with the exception of a couple of other iproperties pulled from the part and placed in adjoining cells (they are working fine, except they end up in row 509...) 

Have you seen this before? I am assuming it is something in the spreadsheet causing the problem, but I am unsure.

Thank you again for the code and any advice you may have on my issue with the used range of the rows.

[Curtis_Waguespack · Accepted solution]

Hi @D.Wheeler3GADA 

The code is looking at the UsedRange so I'm guessing that you have one or more blank rows that are being seen as used by Excel.

For iRow = 2 To oWS.UsedRange.Rows.Count

You might try the suggestions at this link to clean up the blank rows :

https://www.avantixlearning.ca/microsoft-excel/how-to-delete-blank-rows-in-excel-great-strategies-tr...

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

[D.Wheeler3GADA]

@Curtis_Waguespack   Thank you yet again. I had been clearing the cell contents. It never occurred to me to delete the cells that were "holding value" (actually I thought "clearing" the cells would accomplish removing any value). The spreadsheet and code now function exactly as I had wanted. 

Now my PM has tasked me with expanding on the functionality. I don't believe what we would like is possible, but I am going to research it and see what I can find...

Have a great day!

[D.Wheeler3GADA]

@Curtis_Waguespack ,

        Good morning! I am re-visiting this code in the hopes of retooling it for use in a trigger event that will update an excel "drawing log". I have had this code functional in the past, but I am now getting an ActiveX Component error that reads:

This is the Line 8 code for defining the Excel Application object:

excelApp = CreateObject("Excel.Application")

Do you have any insight as to why this snippet is now failing?

Thank you!