Tuesday, June 10, 2014

iLogic - TitleBlock Project Data From Excel

You have a title block that uses prompted entry, and you'd like to fill those fields in from data that is in a spread sheet. You know that the best way to set up a title block is to use the model file's iProperties, but unfortunately you can't make changes to the title block that you've been provided. But there must be a better way than just editing each sheet and re-typing the same information over and over, right?

Here is an iLogic rule that asks the user to browse for an spreadsheet. Then it reads in the data and matches the field name to the title block field, and if a match is found it writes the field value to the title block's prompted entry field.

Here is an example spread sheet:

Here is an example Title Block:

 And here you can see the field text in the title block, with those that are prompted entries denoted by carats: < >

So the iLogic code gets the information from the spreadsheet and then writes it to the title block for every sheet in the drawing.

I've added some lines to activate each drawing sheet and a delay timer to step through each text field so that you can watch the changes, but all of that could be removed if you'd prefer to speed up the running of the rule.

'Browse for the Excel file
Dim oFileDlg As inventor.FileDialog = Nothing
oFileDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oFileDlg.DialogTitle = "Select a Project Information Excel File"
oFileDlg.InitialDirectory = ThisDoc.Path
oFileDlg.CancelError = True
On Error Resume Next
If Err.Number <> 0 Then
'exit if file not selected
ElseIf oFileDlg.FileName <> "" Then
myXLS  = oFileDlg.FileName
End If

'look at sheet1 of the spreadsheet
GoExcel.Open(myXLS, "Sheet1")

'define the drawing
Dim oDoc As DrawingDocument
oDoc = ThisApplication.ActiveDocument
Dim oSheet As Sheet
Dim oPromptEntry

'gather the current sheet name
Dim oCurrentSheet
oCurrentSheet = oDoc.ActiveSheet.Name

'step through each sheet
i = 0
For Each oSheet In oDoc.Sheets
  i = i+1
  'activate the sheet
    For Each oTextBox In oTitleBlock.Definition.Sketch.TextBoxes
                'look at the first 100 rows of the Excel file
                'start at row 2, since row 1 contains headings
                For rowCheck = 2 To 100
            'read the value of the column A
                Dim myCell  As String
            myCell = "<" & GoExcel.CellValue("A" & rowCheck) & ">"
                'compare the titleblock field name to the value in column A
                If myCell = oTextBox.Text Then
                        'get the value from column B
                                oPromptEntry  = GoExcel.CellValue("B" & rowCheck)      
                        'set the prompted entry value
                                Call oTitleBlock.SetPromptResultText(oTextBox, oPromptEntry)
                        '______ add a small delay between text field updates
                                PauseTime = 0.3 'seconds
                                Start = Timer
                        Do While Timer < Start + PauseTime
                                'present a status bar message
                                ThisApplication.StatusBarText = _
                                oDoc.ActiveSheet.Name & " is updating...."
                        '______ end of timer code
                End If
'return to original sheet
'clear the status bar message
ThisApplication.StatusBarText = ""