Ilogic Excel Next Available Line

[Chirpyboy2060647]

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!

[Curtis_Waguespack · Accepted solution]

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	

[Chirpyboy2060647]

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?

[Curtis_Waguespack · Accepted solution]

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

[MechMachineMan]

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)

[Chirpyboy2060647]

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") 

[Chirpyboy2060647]

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