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?
Solution:
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
InventorVb.Application.CreateFileDialog(oFileDlg)
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
oFileDlg.ShowOpen()
If Err.Number <> 0 Then
'exit if file not selected
Return
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
ThisApplication.ActiveDocument.Sheets.Item(i).Activate
oTitleBlock=oSheet.TitleBlock
oTextBoxes=oTitleBlock.Definition.Sketch.TextBoxes
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
ThisApplication.UserInterfaceManager.DoEvents
'present a status bar message
ThisApplication.StatusBarText = _
oDoc.ActiveSheet.Name & " is updating...."
Loop
'______ end of timer code
End If
Next
Next
Next
'return to original sheet
ThisApplication.ActiveDocument.Sheets.Item(oCurrentSheet).Activate
'clear the status bar message
ThisApplication.StatusBarText = ""
