Title Block Update

[donaldleigh]

Hi everyone

I would like to update my title blocks for a project via Excel

Can someone please help me with an iLogic Rule?

What I’m trying to do is run the rule on a drawing.

I have an excel spreadsheet with all my data. The 1st Column has all the Data Names, IE Drawing Number, Client, Project ect. Each column after that is related to each drawing.

What I need is to have the rule look at the file name of the .IDW find that in the 1st row. Once found all the other data will be below that.

I have attached an example of the spreadsheet.

Also here is a rule that sort of works. This only get the data from the 2nd column. also need it to find the spreadsheet that is in the same folder as the drawing

Thanks heaps everyone

 SyntaxEditor Code Snippet

GoExcel.Open("S:\Projects\2017\17024 - Oxley Creek\17024-03\Project Data.xlsx", "Sheet1")
RowStart = 1
RowEnd = 100
For countA = RowStart To (RowEnd * 0.01) + RowStart
    If Not String.IsNullOrEmpty(GoExcel.CellValue("A" & RowEnd)) Then
        RowEnd = RowEnd + 100
    Else
        Exit For
    End If
Next

For rowPN = RowStart To RowEnd
    If String.IsNullOrEmpty(GoExcel.CellValue("A" & RowPN)) Then
    rowN = rowPN - 1
    Exit For
    End If
Next
For oRow = RowStart To RowN
    Dim oName As String = GoExcel.CellValue("A" & oRow)
    iProperties.Value("Custom", oName) = GoExcel.CellValue("B" & oRow)
Next
GoExcel.Close
InventorVb.DocumentUpdate()

[Anonymous]

Have you tried the GoExcel.FindRow functions?

"i" would be the row number to pull all the other data from.

You'll also need to set up GoExcel.TitleRow which is the row number with all your column names in it, and GoExcel.FindRowStart, which is the row number of the first row with data in it, in order for the FindRow function to work correctly.

i = GoExcel.FindRow("filename.xls", "Sheet1", "Data Names", "=", ThisDoc.FileName(False))

Hope this helps,

Dave

[MechMachineMan]

Here is a rule that will update the ACTIVE document if it's file name is found in the spreadsheet. Anything additional between the "\" and the "." of the file name will cause it to not be found.

This method completely skips using iLogic and instead uses a LateBound Instance of Excel to perform the search and fetch the info.

The added sub acts to change the custom iProps because they will fail if you try to change the value when it doesn't exists, so it gets a little messy trying to do that without creating an additional sub to clean up the code. YOU SHOULD NOT HAVE TO TOUCH ANYTHING WITHIN the ChangeCustomProp sub.

Additionally, if there are any other iProps you need to change to, please follow this link and tweak as outlined in there.

http://modthemachine.typepad.com/my_weblog/2010/02/accessing-iproperties.html

Note: to do this, all you should have to do is change the fields "Design Tracking Properties" and "Project" within sub main.

Finally, before writing this code, I took your excel file and transposed the data in it so each "property" is now in it's own column, and the file names are all listed in Column A, with the respective info for that file in the following column.

Good luck.

Sub Main()

'Excel Initialization
	oFile = "C:\Users\Desktop\Project Data.xlsx"
	oSheet = "Sheet1"
	oRowStart = 2
	
	xlApp = CreateObject("Excel.Application")
	xlwb = xlApp.WorkBooks.Open(oFile)
	xlws = xlwb.Worksheets(oSheet)
	
	With xlws
		oRowEnd = .Cells(.Rows.Count, 1).End(-4162).Row  'xldirection.xlup
	End With

'Process inventor file
	Dim oDoc As Document
	oDoc = ThisDoc.Document
	oInvFileName = System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName)

'Iterate through each row
	For RowNumber = oRowStart To oRowEnd
	
'Compare Row to File name
		If xlws.Cells(RowNumber,1).Value = oInvFileName Then
			oFound = True
			ChangeCustomProp(oDoc, "Drawing Number", xlws.Cells(RowNumber, 2).Value)
			ChangeCustomProp(oDoc, "Job Number", xlws.Cells(RowNumber, 3).Value)
			ChangeCustomProp(oDoc, "Title 1", xlws.Cells(RowNumber, 4).Value)
			ChangeCustomProp(oDoc, "Title 2", xlws.Cells(RowNumber, 5).Value)
			ChangeCustomProp(oDoc, "Title 3", xlws.Cells(RowNumber, 6).Value)
			oDoc.PropertySets("Design Tracking Properties")("Project").Value = xlws.Cells(RowNumber, 7)
			ChangeCustomProp(oDoc, "Client", xlws.Cells(RowNumber, 8).Value)
		End If
	Next
	
	If oFound = False Then
		MsgBox("Name of this file not found in file name column!" & vbLf & _
			   "File Name Searched For: " & oInvFilename)
	End If
	
	xlApp.Quit
	xlApp = Nothing
	oDoc.Update
End Sub

Sub ChangeCustomProp(Doc As Document, PropName As String, Value As String)
	Dim oCustomPropSet As Inventor.PropertySet
	oCustomPropSet = Doc.PropertySets("Inventor User Defined Properties")
	
	On Error Resume Next
		oCustomPropSet.Item(PropName).Value = Value
	
	If Err.Number <> 0
		oCustomPropSet.Add(Value, PropName)
                Err.Clear
	End If
End Sub