Because you don't use Vault and don't use an MRP or ERP system you utilize a simple spreadsheet as a part number log to record part numbers and descriptions. This works well, but you'd like to use iLogic to read the spreadsheet and copy in the information you've already entered, rather than having to re-type it or copy/paste it.
Solution:
You can use the GoExcel.Open function in iLogic to do this. I use this approach at home for a simple and effective drawing log. It allows me to maintain consistency between the drawing log and my models. Having the information in the spreadsheet allows me to search and index past designs quickly. Here's an example:
This spreadsheet contains just two columns. One for the Part Number and one for the Description.
The first thing needed in the iLogic rule is a line to locate the spreadsheet, such as:
GoExcel.Open("J:\My Drawing Log.xls", "Sheet1")
When the iLogic rule is triggered, it follows the GoExcel address. I then want to tell it what infromation to read from the sheet. I'll add a For/Next function to it, to look at Column A and find the first empty cell in that column. In the example above this would be cell 8A.
'index row 2 through 10000
For rowPN = 2 To 10000
'find first empty cell in column A
If (GoExcel.CellValue("A" & rowPN) = "") Then
'create a variable for the cell value that is one row less than the empty cell row
lastPN = GoExcel.CellValue("A" & rowPN - 1)
lastDesc = GoExcel.CellValue("B" & rowPN- 1)
Exit For
End If
Next
In the snippet above the variable called lastPN reads the value of the cell that is one less the first empty cell in Column A. Then a variable called lastDesc is set to the value in the same row but for column B.
So in this case iLogic would read in the Part Number: 09-0805 and the Description: hardware, latch, cam action
In order to write these values to the model I'll use this bit of code:
'check to see if Part Number is the same as the file name or blank
If iProperties.Value("Project", "Part Number") = ThisDoc.FileName(False) Or iProperties.Value("Project", "Part Number") = "" Then
'set iProperty to value read in from excel
iProperties.Value("Project", "Part Number") = lastPN
'set iProperty to value read in from excel
iProperties.Value("Project", "Description") = lastDesc
Else
End If
This checks the information in spreadsheet against the iProperties of the model and then writes the values of the target cells to them if the part number is the same as the file name (less the file extension) or if the part number is empty.
To add a new part number to my drawing log, I would simply fill in the Part# and Description for the next row, and then save the spreadsheet:
Now when the rule is run, this new Part Number and Description are looked up and written to the model file iProperties.
Next, I've added a confirmation dialog box with Yes/No buttons to the end of the rule to allow me to accept or reject the returned values (in case I run the rule without remembering to save the spreadsheet after adding a new row, etc.)
'show results and ask user to confirm results
question = MessageBox.Show("PN: " & lastPN & vbLf _
& "Description: " & lastDesc & vbLf _
& "Is this correct?", "iLogic from Excel", MessageBoxButtons.YesNo )
& "Description: " & lastDesc & vbLf _
& "Is this correct?", "iLogic from Excel", MessageBoxButtons.YesNo )
'if answer is no
If question = vbNo Then
'clear these iProperties
iProperties.Value("Project", "Part Number") = ""
iProperties.Value("Project", "Description") = ""
'run rule again
Goto StartRule
'if answer is yes exit rule
Else End If
If the information is incorrect I click No and the Part Number and Description iProperties are cleared and then the rule is run from the beginning again. If the information is correct, then the rule exits.
Note that if an empty cell is found further up the list, the iLogic rule will stop at it and read in the row above it. In the example below a cell in column A has been cleared and the spreadsheet saved.
Now the rule will return the part number and description for row 4, since cell 5A is empty. I often use this to update existing models by inserting a blank row below the changed part number and then running the rule in the corresponding model. Then I simply remove the empty row when done.
Here is the code for the complete rule:
'---------start of iLogic code---------
StartRule:
'read excel file
GoExcel.Open("J:\My Drawing Log.xls", "Sheet1")
'index row 2 through 10000
For rowPN = 2 To 10000
'find first empty cell in column A
If (GoExcel.CellValue("A" & rowPN) = "") Then
'create a variable for the cell value that is one row less than
'the empty cell row
'the empty cell row
lastPN = GoExcel.CellValue("A" & rowPN - 1)
lastDesc = GoExcel.CellValue("B" & rowPN- 1)
Exit For
End If
Next
'check to see if Part Number is the same as the file name or blank
If iProperties.Value("Project", "Part Number") = ThisDoc.FileName(False) _
Or iProperties.Value("Project", "Part Number") = "" Then
Or iProperties.Value("Project", "Part Number") = "" Then
'set iProperty to value read in from excel
iProperties.Value("Project", "Part Number") = lastPN
'set iProperty to value read in from excel
iProperties.Value("Project", "Description") = lastDesc
Else
End If
'show results and ask user to confirm results
question = MessageBox.Show("PN: " & lastPN & vbLf _
&"Description: " & lastDesc & vbLf _
& "Is this correct?", "iLogic from Excel", MessageBoxButtons.YesNo )
'if answer is no
If question = vbNo Then
'clear these iProperties
iProperties.Value("Project", "Part Number") = ""
iProperties.Value("Project", "Description") = ""
'run rule again
Goto StartRule
'if answer is yes exit rule
Else End If
'---------end of iLogic code---------
