Monday, June 20, 2011

iLogic: Use a Part iProperty to Get a File Path

You want to use a project number iProperty found in your Inventor file to automatically look up an Excel spreadsheet file that relates to the project. Then you'd like to retrieve some information from the Excel file and use it in your iLogic or part iProperties.

Here is a bit of sample code that checks the Project iProperty in the current Inventor file and makes sure it is not empty. A line such as: trim(iProperties.Value("Project", "Project")) = "" checks for an empty value or a value that contains only spaces. This is done by using the VB trim function to trim off errant space characters.

If the iProperty is empty, the user is prompted to fill it in.

If there is an existing Project number value then it is used in the path for the XLS file. Then the code makes sure the path to the XLS file is valid. If it's not the user is notified and asked if they'd like to enter it again.

If the XLS file path is valid, then the code reads from it and in this example writes the value of cell B1 through B6 to the iProperties of the Inventor file.

Here is an example of the XLS file to be read by the iLogic rule:

 And here are the results of the Inventor file's iProperties after the rule is run:

'--------Start of iLogic Code----------------------------------------------------
ProjectNo = iProperties.Value("Project", "Project")

'check for empty value
if trim(ProjectNo) = ""  Then
'get value from user and write it to the project iproperty
ProjectNo = InputBox("Enter the Project Number", "iLogic", ProjectNo )
End if

'path of excel file
' example J:\Projects\8888\Project Details.xls
myPath = "J:\Projects\" & iProperties.Value("Project", "Project") "\Project Details.xls"

'check to see that the xls file exists
If Dir(myPath) <> "" Then

          'if the xls file exists, then read from it
            GoExcel.Open(myPath, "Sheet1")
          'do something with the values read from the xls, in this example cells B1 thru B6 are
            'written to the ipropeties of the Inventor file
            iProperties.Value("Summary", "Title")= GoExcel.CellValue("B1")
          iProperties.Value("Summary", "Category")= GoExcel.CellValue("B2")
          iProperties.Value("Summary", "Manager")= GoExcel.CellValue("B3")
          iProperties.Value("Summary", "Company")= GoExcel.CellValue("B4")
          iProperties.Value("Summary", "Subject")= GoExcel.CellValue("B5")
          iProperties.Value("Summary", "Comments")= GoExcel.CellValue("B6")

          'if the xls file does not exist, alert the user and ask if they want to re-enter the project number
            question = MessageBox.Show("This file does not exist:" & vblf & _
            myPath & vblf & "Do you want to re-enter the Project Number?","iLogic",MessageBoxButtons.YesNo)
          if question = vbyes then
               iProperties.Value("Project", "Project") = InputBox("Enter the Project Number", "iLogic", ProjectNo )
               MessageBox.Show("Project Number has been updated to: " _
                 & iProperties.Value("Project", "Project") & vblf & _
                 "Click OK to run the rule again to set iProperty values from the XLS file.", "iLogic")
               Goto StartRule
          End If
End If
'--------End of iLogic Code----------------------------------------------------