topic Re: Ilogic Excel Next Available Line in Inventor Programming Forum

Ilogic Excel Next Available Line

Chirpyboy2060647 — Fri, 05 Feb 2016 20:01:05 GMT

Hey all, So the task is on me now to deal with our engineering stamp request log. And I've got the code to pull what information i want...thats taken care of. My question now is, is there a code that will fill the next available line. So if A1-D1 is filled it will pull my properties to the next open line A2-D2? Without me having to manually change this every time.

Thanks!

Re: Ilogic Excel Next Available Line

Curtis_Waguespack — Fri, 05 Feb 2016 20:50:15 GMT

Hi Chirpyboy2060647,

I'm pretty sure I've seen an example that only looks at the rows in use and finds the last used, but this is what I had on hand. It looks at the first 2000 rows and finds the last used.

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

'open the spreadsheet
GoExcel.Open("MyExcelFile.xls"), "Sheet1")

'Define Range
oRowStart = 2
oRowEnd = 2000
Dim count As Double
For count = oRowStart To oRowEnd
	' If it's blank count it
	If String.IsNullOrEmpty(GoExcel.CellValue("A" & count)) Then 
		i = i + 1
	End If
Next
' Next empty row is max rows minus blank rows plus one
oEmptyRow = oRowEnd - i + 1

Re: Ilogic Excel Next Available Line

Chirpyboy2060647 — Fri, 05 Feb 2016 22:01:05 GMT

Oh nice, ill mess around with this and see how it works for me.

'open the spreadsheet
ExcelFile =  "Test PO" & ".xlsx"

GoExcel.CellValue(ExcelFile, "Sheet1", "K2")  = iProperties.Value("Custom", "FI_005") 'Drawing Number
GoExcel.CellValue(ExcelFile, "Sheet1", "K1")  = iProperties.Value("Custom", "FI_007") 'Proj Name

Actually heere is a sample of what im using to pull to a template excel. If you could guide me on how i would modify this ( i can change the rest of what im pulling or need to) to go inline with your code?

Re: Ilogic Excel Next Available Line

Curtis_Waguespack — Fri, 05 Feb 2016 22:58:22 GMT

Hi Chirpyboy2060647,

So once the Empty Row is found (per the earlier example), you can use something like this to fill in each cell for that row, with the given iProperty.

Note that there is no need to call out the Excel file and Sheet again once it's been established in the code (Autodesk doesn't really do a great job of telling us that in the ilogic documenation or snippets).

GoExcel.CellValue("B" & oEmptyRow) = iProperties.Value("Project", "Part Number") 
GoExcel.CellValue("C" & oEmptyRow) = iProperties.Value("Project", "Revision Number")
GoExcel.CellValue("D" & oEmptyRow) = iProperties.Value("Status", "Engr Date Approved")

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

Re: Ilogic Excel Next Available Line

MechMachineMan — Sat, 06 Feb 2016 03:14:03 GMT

Call me different, but I like to avoid the iLogics whenever possible. Instead, the easiest way I have found is something LIKE:

xlapp = GoExcel.Application
oLastRow = xlapp.ActiveWorkbook.Activesheet.UsedRange.Rows.Count

Which is actually kind of a mesh between the two; avoids the marshal call to get the excel app, but still uses the native excel API to perform the functions that you want, which makes more sense to me.

Then you can also access rows based off of numbers instead of having to have something solid/extra functions for alphanumeric;

Ie;

ActiveWorkBook.ActiveSheet.Cells(1,1).Value = "Text"

where (1,1) is (rowNumber, columnNumber)

Re: Ilogic Excel Next Available Line

Chirpyboy2060647 — Thu, 18 Feb 2016 16:54:12 GMT

Hi Curis,

Sorry for the late response of if this works or not, got really busy at work so i couldnt mess with this code for a bit. When i go to run the code i get the error

Error in rule: Next Line, in document: ORD-19948-A.dwg

Unable to cast object of type 'System.String' to type 'System.Object[,]'.

Here is my code right now

'open the spreadsheet
GoExcel.Open("\\0-File Transfer\Brian\Line.xlsx", "Sheet1")

oRowStart = 2
oRowEnd = 2000
Dim count As Double
For count = oRowStart To oRowEnd
    ' If it's blank count it
    If String.IsNullOrEmpty(GoExcel.CellValue("A" & count)) Then 
        i = i + 1
    End If
Next
' Next empty row is max rows minus blank rows plus one
oEmptyRow = oRowEnd - i + 1

GoExcel.CellValue("B" & oEmptyRow) =  iProperties.Value("Custom", "FI_005")
'GoExcel.CellValue(ExcelFile, "Sheet1", "K2")  = iProperties.Value("Custom", "FI_005")

Re: Ilogic Excel Next Available Line

Chirpyboy2060647 — Wed, 24 Feb 2016 19:56:54 GMT

This actually worked just fine, but ILogic doesn't like when you copy and paste and gives weird spacing. But works perfectly Thank you