Export parts List to Excel

donaldleigh · ‎05-04-2020

Hi all

We have just upgraded from Inventor 2014 straight to 2021.

We export our parts list (Drawing Parts list) to excel and in the template we have a column set up with a formula to check some data is correct. When exporting from 2014 this worked well but since upgrading when exporting it now removed the formula all together.

Am i missing something??

We use Excel 2010

I have included our export rule below

 
Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet
oSheet = oDrawDoc.ActiveSheet

Try 
	Dim oPartslistCheck As PartsList
	oPartslistCheck = oSheet.PartsLists(1)
	partslistpresent = True
Catch
	partslistpresent = False
End Try

If partslistpresent = True Then
	'Set a reference to the first parts list on the active sheet. This assumes that a parts list is on the active sheet.
	Dim oPartList As PartsList
	oPartList = oDrawDoc.ActiveSheet.PartsLists.Item(1)

	Dim oPartNumber As PartsListCell
	Dim oType As PartsListCell
Else
	MessageBox.Show("No Parts List Found", "Title")
	Return
End If

'Get the path and name of the drawing file
path_and_name = ThisDoc.PathAndFileName(False) ' without extension

'Define oDoc
oDoc = ThisDoc.Document

'Specify the drawing sheet
oSheet = oDoc.Sheets("Sheet:1") ' sheet by name

'Say there is a Partslist on the sheet.
oPartslist = oSheet.PartsLists(1)

'Create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'Specify an existing template file to use For formatting colors, fonts, etc
oOptions.Value("Template") = "S:\CAD\Standards\_Template Library\Excel\Ostendo Parts List.xls"

'Specify the columns to export
oOptions.Value("ExportedColumns") = "Project Number;Job Number;Ostendo Part Number;Ostendo Descriptor;DESCRIPTION;QTY;BASE UNIT;DRAWING No.;MATERIAL;Ostendo Total Qty;PART LENGTH/M2 (REF)"

'Specify the start cell
oOptions.Value("StartingCell") = "A7"

'Specify the XLS tab name here the file name is used
oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension

'Choose to include the parts list title row in this example "Ye Old List of Parts" is written to the StartingCell
oOptions.Value("IncludeTitle") = False

'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True

'check for existing XLS file and delete it if found
If Dir(path_and_name & " - Ostendo Parts List.xls") <> "" Then
Kill (path_and_name & " - Ostendo Parts List.xls")
Else
End If

'export the Partslist to Excel with options
oPartslist.Export(path_and_name & " - Ostendo Parts List.xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

MessageBox.Show("Ostendo Parts List Created", "Title")

k14348 · ‎05-04-2020

Hi,

Starting cell A7 is it correct? excel format xls or xlsx? when you do manual export with the same template what result you r getting? So this will give you clue. also check in inventor any other further option added in export options.

-Karth

donaldleigh · ‎05-04-2020

Hi Karth

Yes starting at A7 is correct. We have some data at the top of the excel template.

In 2014 xls was the only format that worked so have left it the same. I have tried xlsx and that gives the same result

I have also exported manually and this removed the formula also.

I will look into options

Donald

Anonymous · ‎05-05-2020

I assume this is one of the many bugs in the 2021 release... We cant export using a template either and it is causing major headaches.. so it isnt your code... its Inventor 2021

marcin_otreba · ‎05-06-2020

Hi,

it works fine, but rule must be correct to do so.

Also be very carefoul because exported columns option:

oOptions.Value("ExportedColumns") = "Project Number;Job Number;Ostendo Part Number;Ostendo Descriptor;DESCRIPTION;QTY;BASE UNIT;DRAWING No.;MATERIAL;Ostendo Total Qty;PART LENGTH/M2 (REF)" is letter case sensitive, so if you write any letter wrong, or there will be additional space in name or that column will not be present in partlist then you will get error.

In one of companies i did something simmilar and i did it by creating partlist style in template drawing file an script was fired from assembly. In the vbacround new drawing was opened and in that drawind i placed partlist with correct style wchich have all needed columns with correct order. This solution is much more flexible than yours.

@donaldleigh corrected script:

Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet
oSheet = oDrawDoc.ActiveSheet

Try 
	Dim oPartslistCheck As PartsList
	oPartslistCheck = oSheet.PartsLists(1)
	partslistpresent = True
Catch
	partslistpresent = False
End Try

If partslistpresent = True Then
	'Set a reference to the first parts list on the active sheet. This assumes that a parts list is on the active sheet.
	Dim oPartsList As PartsList
	oPartsList = oSheet.PartsLists.Item(1)

		'Get the path and name of the drawing file
	path_and_name = ThisDoc.PathAndFileName(False) ' without extension

	'Create a new NameValueMap object
	oOptions = ThisApplication.TransientObjects.CreateNameValueMap

	'Specify an existing template file to use For formatting colors, fonts, etc
	oOptions.Value("Template") = "S:\CAD\Standards\_Template Library\Excel\Ostendo Parts List.xls"

	'Specify the columns to export
	oOptions.Value("ExportedColumns") = "Project Number;Job Number;Ostendo Part Number;Ostendo Descriptor;DESCRIPTION;QTY;BASE UNIT;DRAWING No.;MATERIAL;Ostendo Total Qty;PART LENGTH/M2 (REF)"

	'Specify the start cell
	oOptions.Value("StartingCell") = "A12"

	'Specify the XLS tab name here the file name is used
	oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension

	'Choose to include the parts list title row in this example "Ye Old List of Parts" is written to the StartingCell
	oOptions.Value("IncludeTitle") = False

	'choose to autofit the column width in the xls file
	oOptions.Value("AutoFitColumnWidth") = True

	'check for existing XLS file and delete it if found
	If Dir(path_and_name & " - Ostendo Parts List.xls") <> "" Then
	Kill (path_and_name & " - Ostendo Parts List.xls")
	Else
	End If

	'export the Partslist to Excel with options
	oPartsList.Export(path_and_name & " - Ostendo Parts List.xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

	MessageBox.Show("Ostendo Parts List Created", "Title")
Else
	MessageBox.Show("No Parts List Found", "Title")
	Return
End If

Anonymous · ‎05-06-2020

We dont use a rule to export.... we just 'right-click' and export that way... still doesnt work...

However... i tried your code and it just returns an error (see below) what version of Inventor & Excel are you using?

@marcin_otreba wrote:
Hi,

it works fine, but rule must be correct to do so.
Also be very carefoul because exported columns option:
oOptions.Value("ExportedColumns") = "Project Number;Job Number;Ostendo Part Number;Ostendo Descriptor;DESCRIPTION;QTY;BASE UNIT;DRAWING No.;MATERIAL;Ostendo Total Qty;PART LENGTH/M2 (REF)" is letter case sensitive, so if you write any letter wrong, or there will be additional space in name or that column will not be present in partlist then you will get error.

In one of companies i did something simmilar and i did it by creating partlist style in template drawing file an script was fired from assembly. In the vbacround new drawing was opened and in that drawind i placed partlist with correct style wchich have all needed columns with correct order. This solution is much more flexible than yours.

marcin_otręba · ‎05-06-2020

it is export columns problem. Also idk if you have excel template listed in logic?

oOptions.Value("ExportedColumns") = "Project Number;Job Number;Ostendo Part Number;Ostendo Descriptor;DESCRIPTION;QTY;BASE UNIT;DRAWING No.;MATERIAL;Ostendo Total Qty;PART LENGTH/M2 (REF)" is letter case sensitive, so if you write any letter wrong, or there will be additional space in name or that column will not be present in partlist then you will get error.

can you provide drawing ?

2020, excel does not matter

Hi, maybe you want to check my apps:

DrawingTools View&ColoringTools MRUFolders

Anonymous · ‎05-06-2020

If you read closer, this is an issue with 2021.... 2020 works fine

@marcin_otręba wrote:

2020, excel does not matter

donaldleigh · ‎05-06-2020

Morning all

I'm know that the oOptions.Value("ExportedColumns") is case sensitive. That's not the issue. I have the export working right. my issue is that in column "M" on the template excel file we have a formula (See attached) . When using the rule with Inventor 2014 the formula would work in the exported spreadsheet, but since upgrading to Inventor 2021 the formula now is removing in the export.

@marcin_otręba not sure what you changed in your rule you posted back but that did not work also.

I think this is a Inventor issue

Cheers

Donald

marcin_otręba · ‎05-07-2020

@donaldleigh:

This behavior (removing formulas) was present from as long as i remember i am sure that 2015 was already like this. I do not treat this as an bug, it just how it works.

Export troug ilogic to excel is possible from version 2021, i checked.

If you want to have formulas you can open excel and place it in. Check code below.

If you need something else, give me know

Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet
oSheet = oDrawDoc.ActiveSheet

Try 
	Dim oPartslistCheck As PartsList
	oPartslistCheck = oSheet.PartsLists(1)
	partslistpresent = True
Catch
	partslistpresent = False
End Try

If partslistpresent = True Then
	'Set a reference to the first parts list on the active sheet. This assumes that a parts list is on the active sheet.
	Dim oPartsList As PartsList
	oPartsList = oSheet.PartsLists.Item(1)

		'Get the path and name of the drawing file
	path_and_name = ThisDoc.PathAndFileName(False) ' without extension

	'Create a new NameValueMap object
	oOptions = ThisApplication.TransientObjects.CreateNameValueMap

	'Specify an existing template file to use For formatting colors, fonts, etc
	oOptions.Value("Template") = "c:\vault\ustawienia\iss_system\Szablon XLS1.xls"

	'Specify the columns to export
	oOptions.Value("ExportedColumns") = "PART NUMBER;Opis;Mat.;Thickness;Length;Width;Mass;QTY"

	'Specify the start cell
	i=7
	oOptions.Value("StartingCell") = "A" & i

	'Specify the XLS tab name here the file name is used
	oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension

	'Choose to include the parts list title row in this example "Ye Old List of Parts" is written to the StartingCell
	oOptions.Value("IncludeTitle") = False

	'choose to autofit the column width in the xls file
	oOptions.Value("AutoFitColumnWidth") = False

	'check for existing XLS file and delete it if found
	If Dir(path_and_name & " - Ostendo Parts List.xls") <> "" Then
	Kill (path_and_name & " - Ostendo Parts List.xls")
	Else
	End If

	'export the Partslist to Excel with options
	oPartsList.Export(path_and_name & " - Ostendo Parts List.xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
 Dim XL As Object
Dim xlWS As Object
'XL=GoExcel.Open(path_and_name & " - Ostendo Parts List.xls", "Sheet1")

 XL = CreateObject("Excel.Application")
XL.Workbooks.Open (path_and_name & " - Ostendo Parts List.xls")
 xlWs = XL.ActiveWorkbook.ActiveSheet
                   XL.Visible = True


              Try
                Do Until xlWS.Cells(i, 1).value.tostring = ""
            
                xlWS.Cells(i, 13).value = "=if(G" & i & "=""Each"" , ""CORRECT"",If(SUM(J" & i & "/F" & i & ")=If(H" & i & "=""M"",SUM(K" & i & "/1000),K" & i & "),""CORRECT"",""Fix""))"
                ' xlWS.Cells(i, 6).value = "=A" & i
               
             i=i+1
                Loop
				 XL.Visible = True
     Catch
		 Return
	 End Try
	MessageBox.Show("Ostendo Parts List Created", "Title")
Else
	MessageBox.Show("No Parts List Found", "Title")
	Return
End If

@

Hi, maybe you want to check my apps:

DrawingTools View&ColoringTools MRUFolders

donaldleigh · ‎05-07-2020

@marcin_otręba

Thanks for the updated rule.

I would like to change the excel formula a little but cant get it working now. Here is the new formula

=IF(OR(G17="",G17="BASE UNIT",G17="Each"),"BLANK",IF(G17="M",IF(SUM(J17/F17)=IF(G17="M",SUM(K17/1000),K17),"CORRECT","FIX")))

I think I almost have it but I'm not sure what the "13" is in the below part of the rule. Can you please explain?

xlWS.Cells(i, 13).value

I have updated it to below but the formula is blank in the export.

  xlWS.Cells(i, 13).value = "=IF(OR(G" & i & "="",G" & i & "=""BASE UNIT"",G" & i & "=""Each""),""BLANK"",If(G" & i & "=""M"",If(SUM(J" & i & "/F" & i & ")=If(G" & i & "=""M"",SUM(K" & i & "/1000),K" & i & "),""CORRECT"",""FIX"")))"

Cheers

Donald

marcin_otręba · ‎05-07-2020

xlWS.Cells(i, 13).value

It mean that formula will be placed in cell with row number =i and column number =13.

Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet
oSheet = oDrawDoc.ActiveSheet

Try 
	Dim oPartslistCheck As PartsList
	oPartslistCheck = oSheet.PartsLists(1)
	partslistpresent = True
Catch
	partslistpresent = False
End Try

If partslistpresent = True Then
	'Set a reference to the first parts list on the active sheet. This assumes that a parts list is on the active sheet.
	Dim oPartsList As PartsList
	oPartsList = oSheet.PartsLists.Item(1)

		'Get the path and name of the drawing file
	path_and_name = ThisDoc.PathAndFileName(False) ' without extension

	'Create a new NameValueMap object
	oOptions = ThisApplication.TransientObjects.CreateNameValueMap

	'Specify an existing template file to use For formatting colors, fonts, etc
	oOptions.Value("Template") = "c:\vault\ustawienia\iss_system\Szablon XLS1.xls"

	'Specify the columns to export
	oOptions.Value("ExportedColumns") = "PART NUMBER;Opis;Mat.;Thickness;Length;Width;Mass;QTY"

	'Specify the start cell
	i=13
	oOptions.Value("StartingCell") = "A" & i

	'Specify the XLS tab name here the file name is used
	oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension

	'Choose to include the parts list title row in this example "Ye Old List of Parts" is written to the StartingCell
	oOptions.Value("IncludeTitle") = False

	'choose to autofit the column width in the xls file
	oOptions.Value("AutoFitColumnWidth") = False

	'check for existing XLS file and delete it if found
	If Dir(path_and_name & " - Ostendo Parts List.xls") <> "" Then
	Kill (path_and_name & " - Ostendo Parts List.xls")
	Else
	End If

	'export the Partslist to Excel with options
	oPartsList.Export(path_and_name & " - Ostendo Parts List.xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
 Dim XL As Object
Dim xlWS As Object
'XL=GoExcel.Open(path_and_name & " - Ostendo Parts List.xls", "Sheet1")

 XL = CreateObject("Excel.Application")
XL.Workbooks.Open (path_and_name & " - Ostendo Parts List.xls")
 xlWS = XL.ActiveWorkbook.ActiveSheet
                   XL.Visible = True


              Try
                Do Until xlWS.Cells(i, 1).value.tostring = ""
            
                xlWS.Cells(i, 13).value = "=if(OR(G" & i & "="""",G" & i & "=""BASE UNIT"",G" & i & "=""Each""),""BLANK"",If(G" & i & "=""M"",If(SUM(J" & i & "/F" & i & ")=If(G" & i & "=""M"",SUM(K" & i & "/1000),K" & i & "),""CORRECT"",""Fix"")))"
                ' xlWS.Cells(i, 6).value = "=A" & i
               
             i=i+1
                Loop
				 XL.Visible = True
     Catch
		 Return
	 End Try
	MessageBox.Show("Ostendo Parts List Created", "Title")
Else
	MessageBox.Show("No Parts List Found", "Title")
	Return
End If

Hi, maybe you want to check my apps:

DrawingTools View&ColoringTools MRUFolders

Anonymous · ‎05-08-2020

Inventor 2021 is NOT using the parameters, formulas and configurations set up in the templates when exporting the parts list, like it has in previous versions, namely 2020.. Even without using a code... this is definitely a bug or, at best/worse, a change in the new release and, for us, it is a huge issue.... yes, you can export it, but its not using the templates parameters required to import our parts lists to our ERP system.

Anonymous · ‎05-08-2020

I have multiple templates that we export with in order to be able to communicate with our ERP... it is a "table" with the first 4 cells (starting at A1) empty followed by 22 cells that are formula based cells that auto fill the rest of the cells based on the information in the first 4. i developed these a year ago and have worked perfectly until the update. so to say this is not a "bug" is false.

marcin_otręba · ‎05-08-2020

i sent you private message.

Hi, maybe you want to check my apps:

DrawingTools View&ColoringTools MRUFolders

Anonymous · ‎06-17-2020

issue is with 2021 inventor and has been resolved with a "work-around"

https://forums.autodesk.com/t5/inventor-forum/2021-problem-export-parts-list-to-excel-using-template...

donaldleigh · ‎07-08-2020

Hi @Anonymous

Did you get this "Fix" to work. Its not for me

Donald

Community

Export parts List to Excel

Export parts List to Excel

Export parts List to Excel