Iproperties Export to Excel

[mraymond7REU2]

Hi, I am trying to export the iproperties of all the drawings in our drawing folder to an excel sheet, is there an existing way to do this? I have put together an ilogic script from a bunch of other posts but I cant get it to work, I frequently get this error, Unspecified error (Exception from HRESULT: 0X80004005 (E_FAIL)). Any help would be much appreciated.

Sub Main()
	
	
	
	'declare variable for inventory master excel file
	excel_file = "C:\Users\mraymond\Documents\inventory-master.xlsx"
	
	'open excel file
	GoExcel.Open(excel_file, "Sheet1")
	
	'Create folder variable and Select folder with drawings in it
	Dim Folder As New IO.DirectoryInfo("C:\Users\mraymond\Documents\R-Drive Copy\test\")
	
	'Create file list variable that will contain list of files in string format
	Dim FileList As New List(Of String)
	
	'for loop that will loop through each file found in folder variable and add to file list
	For Each File As IO.FileInfo In Folder.GetFiles("*.idw",IO.SearchOption.AllDirectories)
	    If File.FullName.Contains("OldVersions") = False Then
	        FileList.Add(File.FullName)
	    End If
	Next
	
	'display count of files found in folder
	MsgBox(FileList.Count & " Files were found!")
	
	'create variable for document
	Dim oDoc As Document
	
	'for loop that goes through file on the file list variable
	For Each DocName As String In FileList
	    oDoc = ThisApplication.Documents.Open(DocName, True)
		
		'for loop to find the first empty row in the inventory master excel file
		Dim i As Integer
		For i = 2 To 20000
		'find first empty cell in column A
			If (GoExcel.CellValue("A" & i) = "") Then
				'this command will run on each document in the folder variable
				GoExcel.CellValue(excel_file, "Sheet1", "A" & i) = iProperties.Value("Project", "Part Number")
				GoExcel.CellValue(excel_file, "Sheet1", "B" & i) = iProperties.Value("Project", "Revision Number")
				GoExcel.CellValue(excel_file, "Sheet1", "C" & i) = iProperties.Value("Project", "Description")
				Exit For
			Else
				i = + 1
			End If
				
			
		Next
		
	    oDoc.Close
	Next
	
	GoExcel.Save
	GoExcel.Close
	
End Sub

[Darkforce_the_ilogic_guy]

I think the problem with your code might be that it does not run on the fil you want too , but I can  not be sure at this time.  I think I would be able to make the code you need. but there are a few think I need to know.

First of are you sure the properties are on the IDW file ? the system i work on my self thoes not have the have the properties form the part/assambly synz over to the IDW... because we want to save space in the Vault database.

and second you do you know what part of the code that fail ?

[Darkforce_the_ilogic_guy]

I have not fund an code that work jet .. but I have fund some of the problem with the code

first of all 

GoExcel.CellValue(excel_file, "Sheet1", "A" & i) = iProperties.Value("Project", "Part Number")

it does not read the properties on the that you are open .. with the ilogic code .. but the properties on the file that you open to run the file ... but the code do run on my computer and work it does just not read the properties form the right fil.

second thing 

the code fail if excel are not close ... you might have to close all the excel in the joblist

3rd.

It look like it also fail if the cell are not emtry when you start the code.... I am not 100 % sure of this  because it might just have been an Excel program that i have not fund and close..

I use this code to do what your want .. just to open all drawing form an assambly ... maybe you can find a way to rewrite this .. I do not know how at det time and I have to go to bed now... I might look at it again later if i get the time

oRefDocs = oAsmDoc.AllReferencedDocuments
'Dim oRefDoc As Document
cell = 2
For Each oRefDoc In oRefDocs
	
	
	
	cell = cell+1
    idwPathName = Left(oRefDoc.FullDocumentName, Len(oRefDoc.FullDocumentName) -3) & "idw"
	debug(idwPathName.ToString & " " & "NewDrawning")
	'GoExcel.Open(ExcelPath, ExcelSheet)
	'GoExcel.CellValue(ExcelPath, ExcelSheet, "B" + Cell.ToString) = idwPathName
	

    ' Check to see that the model has a drawing of the same path and name
	Dim oDrawDoc As DrawingDocument
	
		'False Grafik off True Grafik on
		'MessageBox.Show(idwPathName, "Title")
		Try
        oDrawDoc = ThisApplication.Documents.Open(idwPathName, False)
		Catch
				ExcelPath  = "C:\Working Folder\CAD\Kallesoe\Kallesoe iLogic\PDFLOg.xlsx"
	ExcelSheet = "Log"
	GoExcel.Open(ExcelPath, ExcelSheet)
	GoExcel.CellValue(ExcelPath, ExcelSheet, "B" + cell.ToString) = Left(oRefDoc.FullDocumentName, Len(oRefDoc.FullDocumentName))
	'GoExcel.CellValue(ExcelPath, ExcelSheet, "B" + cell.ToString) = idwPathName
	Try
		
	GoExcel.CellValue(ExcelPath, ExcelSheet, "C" + cell.ToString) =  iProperties.Value(oRefDoc.DisplayName, "Project", "Description")
	GoExcel.CellValue(ExcelPath, ExcelSheet, "D" + cell.ToString) = iProperties.Value(oRefDoc.DisplayName, "Custom", "Part Description")
	
	
	Catch
		oRefDoc.DisplayName=""
	GoExcel.CellValue(ExcelPath, ExcelSheet, "C" + cell.ToString) = iProperties.Value(oRefDoc.DisplayName, "Project", "Description")
	Try
	GoExcel.CellValue(ExcelPath, ExcelSheet, "D" + cell.ToString) = iProperties.Value(oRefDoc.DisplayName, "Custom", "Part Description")
	Catch
		
	end try
		debug("wrong displaynName " & idwPathName.ToString)
		
	End Try
	GoExcel.Save
	'GoExcel.Close
	
		End Try
    If (System.IO.File.Exists(idwPathName)) Then
				      
		ThisApplication.Documents.Open(idwPathName, True) 
		Dim auto = iLogicVb.Automation
	auto.RunExternalRule(oDrawDoc, "SavePDF")
		oDrawDoc.Close
		oDrawDoc.Close
			ExcelPath  = "C:\Working Folder\CAD\Kallesoe\Kallesoe iLogic\PDFLOg.xlsx"
	ExcelSheet = "Log"
	GoExcel.Open(ExcelPath, ExcelSheet)
	GoExcel.CellValue(ExcelPath, ExcelSheet, "B" + Cell.ToString) = idwPathName
	GoExcel.Save
	
	GoExcel.Close

[fullevent · Accepted solution]

Hello @mraymond7REU2,

regarding your first post. Try this code snippet and see if it works for you.

You just have to put your paths back.

Sub Main()
	
	
	
	'declare variable for inventory master excel file
	excel_file = "C:\Users\Aleksandar.Kr\Desktop\Kurze Übung.xlsx"
	
	'open excel file
	Dim oExcel = CreateObject("Excel.Application")
	Dim oWorkbook = oExcel.Workbooks.Open(excel_file)
	oExcel.Application.Visible = True
	
	'Create folder variable and Select folder with drawings in it
	Dim Folder As New IO.DirectoryInfo("C:\Users\Aleksandar.Kr\Desktop\test123\")
	
	'Create file list variable that will contain list of files in string format
	Dim FileList As New List(Of String)
	
	'for loop that will loop through each file found in folder variable and add to file list
	For Each File As IO.FileInfo In Folder.GetFiles("*.idw",IO.SearchOption.AllDirectories)
	    If File.FullName.Contains("OldVersions") = False Then
	        FileList.Add(File.FullName)
	    End If
	Next
	
	'display count of files found in folder
	MsgBox(FileList.Count & " Files were found!")
	
	'create variable for document
	Dim oDoc As Document
	
	'for loop that goes through file on the file list variable
	For Each DocName As String In FileList
	    oDoc = ThisApplication.Documents.Open(DocName, True)
		
		'for loop to find the first empty row in the inventory master excel file
		Dim i As Integer
		For i = 2 To 20000
		'find first empty cell in column A
			If (oWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "") Then
				'this command will run on each document in the folder variable
				oWorkbook.Sheets("Sheet1").Cells(i, 1).Value = iProperties.Value("Project", "Part Number")
				oWorkbook.Sheets("Sheet1").Cells(i, 2).Value = iProperties.Value("Project", "Revision Number")
				oWorkbook.Sheets("Sheet1").Cells(i, 3).Value = iProperties.Value("Project", "Description")
				Exit For
			Else
				i = + 1
			End If
				
			
		Next
		
	    oDoc.Close
	Next
	
	oWorkbook.Save
	oWorkbook.Close
	
End Sub

regards,

Aleks

[mraymond7REU2 · Accepted solution]

I solved this a while ago and forgot to come back here to post my findings. Below is the code that I put together which has the added benefit of exporting to csv instead of excel so excel doesn't need to be installed on the machine and it prompts for the files to be exported and the csv file to export them to.

Imports System.IO
' Declare the Application object
Dim oApplication As Inventor.Application

' Obtain the Inventor Application object.
' This assumes Inventor is already running.
oApplication = GetObject(, "Inventor.Application")

' FOLDER selection dialog
Dim FilePATH As String = "FilePATH"
Dim dialog As New System.Windows.Forms.FolderBrowserDialog()
dialog.SelectedPath = "R:\Drawings"
dialog.Description = "Select Folder to Pull Drawing Properties From (recursive)"
If dialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
	drawingDir = dialog.SelectedPath
Else
	MsgBox("canceled!")
End If

' FILE Selection dialog
Dim oFileDlg As Inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.InitialDirectory = oOrigRefName
oFileDlg.CancelError = True
On Error Resume Next
oFileDlg.ShowOpen()
If Err.Number <> 0 Then
Return
ElseIf oFileDlg.FileName <> "" Then
selectedfile = oFileDlg.FileName
End If


'Dim drawingDir = "R:\Drawings"
For Each foundFile As String In My.Computer.FileSystem.GetFiles(drawingDir, FileIO.SearchOption.SearchAllSubDirectories, "*.idw")
	ThisDoc.Launch(foundFile)
	
	' Set a reference to the active document.
	' This assumes a document is open.
	Dim oDoc As Document
	oDoc = oApplication.ActiveDocument
	      
	' Obtain the PropertySets collection object
	Dim oPropsets As PropertySets
	oPropsets = oDoc.PropertySets
	
	PartNo = oPropsets.Item("{32853F0F-3444-11d1-9E93-0060B03C1CA6}").ItemByPropId(kPartNumberDesignTrackingProperties).Value
	Revision = oPropsets.Item("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}").ItemByPropId(kRevisionSummaryInformation).Value
	Description = oPropsets.Item("{32853F0F-3444-11d1-9E93-0060B03C1CA6}").ItemByPropId(kDescriptionDesignTrackingProperties).Value

	'Write data info To CSV File
	Dim oAppend As System.IO.StreamWriter
	'selectedfile = "C:\Users\mraymond\Documents\drawings-local\test\inv-master.csv"
	oAppend = IO.File.AppendText(selectedfile)
	oAppend.WriteLine(PartNo & "," & Revision & "," & Chr(34) & Description & Chr(34))
	oAppend.Flush()
	oAppend.Close()
	
	ThisApplication.Documents.CloseAll()
	
Next

[floccipier]

Hi, I have a question, first folder location we select is to make selection of director where drawings are stored, may I ask what selection we should make in second dialogue box?

[sn_cad]

The .csv file to write to.

[mark.martinezECRPT]

This works for me if my property is in Project.  However, if my property is in Custom, it cannot find the property.

For example, I have a Custom property named DESCRIPTION.  When I run the rule, I get an error iProperties: Cannot find a property named "DESCRIPTION".  It does exist in the part. See attachment.

These are my three lines.

oWorkbook.Sheets("Sheet1").Cells(i, 1).Value = iProperties.Value("Custom", "DESCRIPTION")
oWorkbook.Sheets("Sheet1").Cells(i, 2).Value = iProperties.Value("Custom", "MANUFACTURER")
oWorkbook.Sheets("Sheet1").Cells(i, 3).Value = iProperties.Value("Custom", "MANUFACTURERS_PART_NUMBER")

[A.Acheson]

Hi @mark.martinezECRPT 

Those iproperty snippets work great when you are in only one document but if you try to loop through documents the wrong document can get targeted. There is a method to target the iproperty through the filename but it is a little tricky. 

Try the API route see helpful article here. Once you have the document, set up the property sets then retrieve the property. Wrap the process in a try catch to error out if no custom iprop exists.

oDoc = ThisApplication.Documents.Open(DocName, True)
Try
' Obtain the PropertySets collection object 
Dim oPropsets As PropertySets = oDoc.PropertySets 
Dim description As String = oPropsets.Item("Inventor User Defined Properties").Item("DESCRIPTION").Value 
Dim manufacturer As String = oPropsets.Item("Inventor User Defined Properties").Item("MANUFACTURER").Value
Dim manufPartNo As String = oPropsets.Item("Inventor User Defined Properties").Item("MANUFACTURERS_PART_NUMBER").Value

oWorkbook.Sheets("Sheet1").Cells(i, 1).Value = description
oWorkbook.Sheets("Sheet1").Cells(i, 2).Value = manufacturer
oWorkbook.Sheets("Sheet1").Cells(i, 3).Value = manufPartNo 
Catch
End Try
 

[mark.martinezECRPT]

What I am finding out is that the rule runs only on the file that I run the rule on.  It opens all the files, but only the initial file is being searched for the Custom properties.  It doesn't search the properties in the opened files.

[A.Acheson]

Hi @mark.martinezECRPT 

Can you share the whole rule your using?

[mark.martinezECRPT]

After fixing a few lines it runs but only on the first file.  It writes a line to the excel file but then it opens up the next file and freezes with the excel file open.

[A.Acheson]

Hi @mark.martinezECRPT 

It looks like your excel cells are being overwritten for each filename which is why your not seeing results for the remaining files. 

You can use this logger to test results without excel. 

Logger.Info(description & "-" & manufacturer & "-" & manufPartNo)

 You will need to remove the for loop looping over the rows and use a counter to index the rows like below

i=i+1

[mark.martinezECRPT]

The i = i +1 worked great.  Thanks.

What if I want to add Part Number from the Project tab?  Is this the correct way?

Dim partnumber As String = oPropsets.Item("Inventor Project Properties").Item("Part Number").Value

[mark.martinezECRPT]

I found it out.

Design Tracking Properties

Thanks. 

[A.Acheson]

If you have a look at the previous linked article you will find that under "Design Tracking Properties" Property Set. I also converted that VBA retrieval code to ilogic so you can retrieve your own list whenever you like. 

Dim partnumber As String = oPropsets.Item("Design Tracking Properties").Item("Part Number").Value

 Get your own iproperties by retrieving Property and Property Sets:

  Dim doc As Document = ThisApplication.ActiveDocument
  For Each ps As PropertySet In doc.PropertySets
    Logger.Info (ps.Name + " / " + ps.InternalName)
    For Each p As [Property] In ps
      Logger.Info ("  " + p.Name + " /" + Str(p.PropId))
    Next
  Next