- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Can you share the whole rule your using?
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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 as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
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
NextOr if this helped you, please, click (like)
Regards
Alan