Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

Export Part Lists from multiple sheets

Anonymous

Export Part Lists from multiple sheets

Anonymous
Not applicable

I stumbled across some iLogic that I am having a hard time manipulating.  I am looking to export Part Lists from multiple sheets ( pipe spools) of an IDW.  I would like the code to export "SPOOL:*" to separate excel files called SPOOL#*. (Where each "*" is a sheet.)  I am unsure create code that loops to the next sheet while writing the correct excel file.  Any help or advice is appreciated.

 

 

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document

Dim oSheet As Inventor.Sheet
'oSheet = oDoc.Sheets(1) ' first sheet
oSheet = oDoc.Sheets("Sheet:1") ' sheet by name

' say there is a Partslist on the sheet.
Dim oPartslist As PartsList
oPartslist = oSheet.PartsLists(1)

' export the Partslist to Excel.

' folderName = "C:\temp"
folderName = ThisDoc.Path
oPartslist.Export(folderName & "\Partslist.xls", PartsListFileFormatEnum.kMicrosoftExcel)
0 Likes
Reply
Accepted solutions (1)
1,723 Views
12 Replies
Replies (12)

MechMachineMan
Advisor
Advisor
For j=1 to oDoc.Sheets.Count
'Code
If oSheet.PartsLists.Count > 0
'Go on with export.
oExportPath = folderName & oSheet.Name & ".xls"
End if
Next

--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.

Justin K
Inventor 2018.2.3, Build 227 | Excel 2013+ VBA
ERP/CAD Communication | Custom Scripting
Machine Design | Process Optimization


iLogic/Inventor API: Autodesk Online Help | API Shortcut In Google Chrome | iLogic API Documentation
Vb.Net/VBA Programming: MSDN | Stackoverflow | Excel Object Model
Inventor API/VBA/Vb.Net Learning Resources: Forum Thread

Sample Solutions:Debugging in iLogic ( and Batch PDF Export Sample ) | API HasSaveCopyAs Issues |
BOM Export & Column Reorder | Reorient Skewed Part | Add Internal Profile Dogbones |
Run iLogic From VBA | Batch File Renaming| Continuous Pick/Rename Objects

Local Help: %PUBLIC%\Documents\Autodesk\Inventor 2018\Local Help

Ideas: Dockable/Customizable Property Browser | Section Line API/Thread Feature in Assembly/PartsList API Static Cells | Fourth BOM Type
0 Likes

Anonymous
Not applicable

MechMachineMan,

 

I appreciate the quick response.  I'm not really good at this whole iLogic thing.  I thought I interpreted your code correctly.  If I run the code below, a message appear twice that says "SaveAs method of Workbook class failed".  Do you mind elaborating on your code?

 

Thanks.

 

 

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document

Dim oSheet As Inventor.Sheet
'oSheet = oDoc.Sheets(1) ' first sheet
oSheet = oDoc.Sheets("SPOOL:1") ' sheet by name


' say there is a Partslist on the sheet.
Dim oPartslist As PartsList
oPartslist = oSheet.PartsLists(1)


For j=1 To oDoc.Sheets.Count
'Code
If oSheet.PartsLists.Count > 0
'Go on with export.
oPartslist.Export(folderName & oSheet.Name & ".xls", PartsListFileFormatEnum.kMicrosoftExcel)


End If
Next 
0 Likes

MechMachineMan
Advisor
Advisor
It might not need the ".xls". Also, the for loop has to surround the part
where it grabs the parts like if you want it to go through all of the
sheets.

--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.

Justin K
Inventor 2018.2.3, Build 227 | Excel 2013+ VBA
ERP/CAD Communication | Custom Scripting
Machine Design | Process Optimization


iLogic/Inventor API: Autodesk Online Help | API Shortcut In Google Chrome | iLogic API Documentation
Vb.Net/VBA Programming: MSDN | Stackoverflow | Excel Object Model
Inventor API/VBA/Vb.Net Learning Resources: Forum Thread

Sample Solutions:Debugging in iLogic ( and Batch PDF Export Sample ) | API HasSaveCopyAs Issues |
BOM Export & Column Reorder | Reorient Skewed Part | Add Internal Profile Dogbones |
Run iLogic From VBA | Batch File Renaming| Continuous Pick/Rename Objects

Local Help: %PUBLIC%\Documents\Autodesk\Inventor 2018\Local Help

Ideas: Dockable/Customizable Property Browser | Section Line API/Thread Feature in Assembly/PartsList API Static Cells | Fourth BOM Type

Curtis_Waguespack
Consultant
Consultant

Hi rtrayes13,

 

There were a couple of issues with the file path your last code sample:

  1. a backslash was needed between the folder path and the file name
  2. using the sheet name as the xls file name means that a colon is used in the file name, which is not permitted, so we have to remove or replace the colon, In the example below it's replaced with an underscore.

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

 

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document
Dim oPartslist As PartsList
Dim oSheet As Inventor.Sheet

'get folder (using the same path as this doc)
folderName = ThisDoc.Path

'look at each sheet
For Each oSheet in oDoc.Sheets
'get the first parts list on the sheet
oPartslist = oSheet.PartsLists(1)
'replace colon in sheet name with underscore
oName = Replace(oSheet.Name, ":","_") 
'export each parts list
oPartslist.Export(folderName & "\" & oName  & ".xls", PartsListFileFormatEnum.kMicrosoftExcel)
Next 

'tell the user the files were created
MessageBox.Show("New *.xls files created in: " _
&vblf & folderName, "iLogic")

'open the folder where the new folders are saved
Shell("explorer.exe " & folderName & "\" ,vbNormalFocus)

 

Anonymous
Not applicable

Curtis,

 

Thank you.  It worked like a charm.  I do have one final question, I think it would be better served if it were to export the parts list "IF" there was a parts list on the sheet.  I'm not sure if this would work with the code you provided since it seems like it does it for all sheets within the document rather than each sheet individually.  Generally, our drawing packages do not have parts lists on the first and sometime the last sheet.  I apologize it seems like I am asking you to do my dirty work but I appreciate your help.

0 Likes

Curtis_Waguespack
Consultant
Consultant
Accepted solution

Hi rtrayes13,

 

Here is the same rule with a Try/Catch/End Try added that will handle the error created when a sheet doesn't have a parts list.

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

 

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document
Dim oPartslist As PartsList
Dim oSheet As Inventor.Sheet

'get folder (using the same path as this doc)
folderName = ThisDoc.Path


	'look at each sheet
	For Each oSheet in oDoc.Sheets
	'get the first parts list on the sheet
	Try
	oPartslist = oSheet.PartsLists(1)
	'replace colon in sheet name with underscore
	oName = Replace(oSheet.Name, ":","_") 
	'export each parts list
	oPartslist.Export(folderName & "\" & oName  & ".xls", PartsListFileFormatEnum.kMicrosoftExcel)
	Catch
	End Try
	Next 


'tell the user the files were created
MessageBox.Show("New *.xls files created in: " _
&vblf & folderName, "iLogic")

'open the folder where the new folders are saved
Shell("explorer.exe " & folderName & "\" ,vbNormalFocus)

 

0 Likes

Anonymous
Not applicable

Curtis,

 

Thank you very much!

0 Likes

cain.hillier
Participant
Participant

Hi Curtis,

 

Similar to rtrayes13 problem, i would like to export Parts List to excel.

The issue that i have is that i might have more than one part list on a single sheet. 

Firstly, can this be done and if so i am after a code that does;

 

Search all sheet(s) for part list(s)

Import the Part List(s) to excel (preferable all on same excel sheet with a gap between each part list) 

* If there are more than one sheets in idw then excel can be the same.

** Eg idw sheet 1 has 4 part list therefore excel sheet 1 will have 4 parts list all with a gap between them

& if idw sheet 2 has 2 part list then excel sheet 2 will have 2 part list all with a gap between them

 

Save file location in same location as idw. 

 

Thanks in advance.

 

 

 

0 Likes

MechMachineMan
Advisor
Advisor

I think exporting the parts lists always recreates the output excel file, therefore to achieve multiple on one sheet you will need to export to a temporary excel file and then move the data to your working spreadsheet.


--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.

Justin K
Inventor 2018.2.3, Build 227 | Excel 2013+ VBA
ERP/CAD Communication | Custom Scripting
Machine Design | Process Optimization


iLogic/Inventor API: Autodesk Online Help | API Shortcut In Google Chrome | iLogic API Documentation
Vb.Net/VBA Programming: MSDN | Stackoverflow | Excel Object Model
Inventor API/VBA/Vb.Net Learning Resources: Forum Thread

Sample Solutions:Debugging in iLogic ( and Batch PDF Export Sample ) | API HasSaveCopyAs Issues |
BOM Export & Column Reorder | Reorient Skewed Part | Add Internal Profile Dogbones |
Run iLogic From VBA | Batch File Renaming| Continuous Pick/Rename Objects

Local Help: %PUBLIC%\Documents\Autodesk\Inventor 2018\Local Help

Ideas: Dockable/Customizable Property Browser | Section Line API/Thread Feature in Assembly/PartsList API Static Cells | Fourth BOM Type
0 Likes

cain.hillier
Participant
Participant

From my understanding,

 

If there is 4 part lists on a sheet, 

creating a code will produce 4 excel documents and then , you would have to copy all them and place them in a single document?

 

Do you happen to know a code that can export more than one part list on a sheet to separate excel documents?

 

Thanks

0 Likes

tmuel
Advocate
Advocate

I am working through a similar challenge. I have multiple parts lists on multiple sheets. I am looping through using the export parts-list function and pushing the information into a single excel spreadsheet. I have found that if a table (worksheet name) is specified during export to excel, and that table exist it will prompt to overwrite. If a table is not specified it will append another worksheet to the same excel document. This is the behavior I am looking for as a user may need to do this from multiple drawing files, into the same spreadsheet.

 

The result is a spreadsheet with many tabs, then we are pulling that into another single worksheet with a macro. 

 

If you want to push them to spreadsheets with different names, you would probably want to build a naming scheme that included the sheet and parts list loop counter

 

Hopefully that clarifies.

 

 

Tim

 

 

 

 

0 Likes

G.Binl
Advocate
Advocate


Hello Curtis,

I'm new to Ilogic and Macros. I tried the code that you had placed up on the board is there a trick to making it work.  I did just a copy and paste.

we have many spools and bom that we need to export for now I'm doing it manually and grinding it out but it takes so long... this code would help greatly if I can get it to work.

Thanks for any input..

Garrett

 

0 Likes