topic Re: ilogic to find the next free cell in excel in Inventor Programming Forum

ilogic to find the next free cell in excel

Anonymous — Mon, 27 May 2013 21:05:10 GMT

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.

Re: ilogic to find the next free cell in excel

xiaodong_liang — Tue, 04 Jun 2013 03:46:52 GMT

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.

Re: ilogic to find the next free cell in excel

jdkriek — Tue, 04 Jun 2013 15:33:57 GMT

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.

Re: ilogic to find the next free cell in excel

Anonymous — Thu, 06 Jun 2013 13:47:10 GMT

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!!!

Re: ilogic to find the next free cell in excel

jdkriek — Thu, 06 Jun 2013 17:27:28 GMT

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

Re: ilogic to find the next free cell in excel

Anonymous — Wed, 12 Jun 2013 14:13:23 GMT

It worked like a charm jdkriek thanks sooooooooooooo much 🙂

Re: ilogic to find the next free cell in excel

jdkriek — Wed, 12 Jun 2013 14:38:16 GMT

You're quite welcome, glad I could help 😉

Re: ilogic to find the next free cell in excel

MechMachineMan — Fri, 12 Sep 2014 13:30:37 GMT

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

Re: ilogic to find the next free cell in excel

ulas.deniz — Thu, 08 Jun 2023 12:04:15 GMT

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

Re: ilogic to find the next free cell in excel

A.Acheson — Fri, 09 Jun 2023 00:34:33 GMT

Hi @ulas.deniz

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

Re: ilogic to find the next free cell in excel

MechMachineMan — Fri, 09 Jun 2023 02:11:10 GMT

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.

Re: ilogic to find the next free cell in excel

ulasdeniz — Fri, 09 Jun 2023 07:08:27 GMT

Thanks. I did it yesterday like you said.