ilogic to find the next free cell in excel

[Anonymous]

Hi folks,

What im trying to do is take the iproperties from an inventor .dwg, and place them into a specific excel spreadsheet.
I've managed to get so far, by using code that Curtis posted, which does the opposite of what im looking to do.The code Curtis posted takes info from excel and fills in iprops.
I can fill in specfic cells, for instance columns A, B & C on Row 1 and place that info into a specific spreadsheet.
But my code falls down when i run the code on another .dwg, It obviously overwrites what was previously in those cells.... I am looking for the code that looks to see if those cells are in use, and if so, go to the next available cell down.
Or if anyone has done this before and would be so kind as to share your knowledge it would be greatly appreciated.
Many thanks.

[xiaodong_liang]

Hi,

It sounds like a question of Excel API only, instead of Inventor API. You may have to consult with Excel. But if you could clarify a bit on "cells are in use", probably some peers here would be able to give some comment.

[jdkriek]

Fleagle,

Can you post your code? There's a few ways to do this, but it depends on your method.

Xiaodong Liang,

He means is the cell empty or not.

[Anonymous]

Hi jdkriek,

 Thanks for your reply, please see below for the code im running (very basic).

StartRule:

 
'read excel file
 Worksheet=("C:\Workspace\00Vault\ACE-02001- ACE-02500\ACE-02380\ACE-02380-080017")
 Sheetno= ("Sheet1")
GoExcel.Open(Worksheet, Sheetno)


For row = 2 To 10000

If GoExcel.CellValue("A" & row) = "" Then GoExcel.CellValue("A" & row) =iProperties.Value("Project", "Part Number")
If GoExcel.CellValue("B" & row) = "" Then GoExcel.CellValue("B" & row) =iProperties.Value("Project", "Revision Number")
If GoExcel.CellValue("D" & row) = "" Then GoExcel.CellValue("D" & row) =iProperties.Value("Status", "Eng. Approved Date" )
If GoExcel.CellValue("F" & row) = "" Then GoExcel.CellValue("F" & row) =iProperties.Value("Project", "Description")


Exit For 

Next



GoExcel.Save

 As you will notice there has to be an excel file already saved in the location specified in the 'Worksheet' section of the code...

Any help would be Brilliant!!!

[jdkriek · Accepted solution]

The code you are using really isn't intended to do what you want.

Wrote this real quick, hope it helps.

Worksheet = ("C:\Workspace\00Vault\ACE-02001- ACE-02500\ACE-02380\ACE-02380-080017")
Sheetno = ("Sheet1")
' Open excel 
GoExcel.Open(Worksheet, Sheetno)
' Define Range
RowStart = 2
RowEnd = 100
For count = RowStart To RowEnd
	' 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
row = RowEnd - i + 1
MsgBox("Inserting Data on Row " & row)
GoExcel.CellValue("A" & row) = iProperties.Value("Project", "Part Number")
GoExcel.CellValue("B" & row) = iProperties.Value("Project", "Revision Number")
GoExcel.CellValue("D" & row) = iProperties.Value("Status", "Eng. Approved Date")
GoExcel.CellValue("F" & row) = iProperties.Value("Project", "Description")
GoExcel.Save

[Anonymous]

It worked like a charm jdkriek thanks sooooooooooooo much  🙂

[jdkriek · Accepted solution]

You're quite welcome, glad I could help 😉

[MechMachineMan]

Hey! I tried using this code and modifying it, but it keeps telling me I can't use the Row type in an expression. (or something along those lines). Was wonder if you could help me out, please and thanks!

The code is:
'creating dummy properties for the user and filename to use in next rule
SharedVariable("Path")= ThisDoc.PathAndFileName(True)
iProperties.Value("Custom", "Draftsman")= ThisApplication.GeneralOptions.UserName

'my attempt at using an input box with a temporary variable to store my change notes
SharedVariable("REVI")= InputBox("What did you change?", "REV", "")

'runs next rule
'iLogicVb.RunRule("EXCEL LOG")

Worksheet = ("C:\Documents\Excel File Log")
Sheetno = ("Sheet1")
' Open excel
GoExcel.Open(Worksheet, Sheetno)
' Define Range
RowStart = 2
RowEnd = 100
For count = RowStart To RowEnd
' 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
row = RowEnd - i + 1
GoExcel.CellValue("A" & row) = TimeString
GoExcel.CellValue("B" & row) = DateString
GoExcel.CellValue("C" & row) = iProperties.Value("Custom", "Draftsman")
GoExcel.CellValue("D" & row) = SharedVariable("REVI")
GoExcel.CellValue("E" & row) = SharedVariable("Path")
GoExcel.Save

[ulas.deniz]

it says  "Error on Line 31 : 'row' is a type and cannot be used as an expression". How can I a solve this?

[A.Acheson]

Hi @ulas.deniz 

Can you post the code your using to determine what line 31 is?

[MechMachineMan · Accepted solution]

Try using the same code, but replace 'row' with 'currow' or something other
than row.

'Row' is a 'protected' word in the code which is why it's getting confused.

[ulasdeniz]

Thanks. I did it yesterday like you said.