Export and Import Materials Library with Excel

[Anonymous]

 I would like to export and import my custom material library with Excel
 
I add materials to custom material library, (tensile strength, yield strength,...) 

[tobias_wiesendanger · Accepted solution]

Sadly there is still no possibility to import materials. For export there are some ways with ilogic / VBA to export to Excel.

regards Tobias

[Anonymous · Accepted solution]

Instead of Export and Import, you can create a new library for all your custom material.

It'll be save in Design Data\Material as a .adsklib file which you can copy to any computer.

Once you did that, add that library in your Project Appearance and Material Library.

You can make it active so it is used by default.

This way you don't make any changes to Autodesk library which got overwrite everytime you install new version.

[guido_overath]

I've the same Need...

I Need to Import/create 600! Special materials to that insane Lib...

[Anonymous]

you can create or import 1000 plus special material using ZetExcel.com so must try it. Thanks

[Anonymous]

how...!

[guido_overath]

Hi  mbaig96233

I found this Workaround:

I defined a IPT part file that contains all material. In that ipt I Manage all materials and copy then the Material to a centralized materials.ADSK file.

The IPJ of all our Engineers is pointing to that centralized folder of Design Data. Even to Templates title blocks and Borders...

Hope this helps.

Cheers

Hint: The Idea to copy all material into Inventor Standard.ipt is not that good. The file size rises that much that this take effect in large assys. we deal with aprox 900 different materials. The centralized materials.ADSK is about 27MB!

The first load takes a while. Therefore I wrote a small iLogic tool that set the material by using a list that is created much quicker that reading data base by Material Manager.

This software is not made for use in large design departments. My further issues in corporate wide use are:

- publishing Inventor settings corporate wide (Copying xml files is not the 1st option I'd like to choose)

- publishing/synchronizing template files 

- publishing IPJ files

- dealing with content Center parts in PLM system like SAP ECTR

- preventing modification of Content Center DB, Materials DB and other files by users.

- ...

Just to display a few of my needs

[tobias_wiesendanger]

I think a good idea would be to programm something that can read an excel file, which has defined formatting and then creates a completly new material library based on that excel. That should be programmable.

Maybe someone has the time to do that 🙂

[Anonymous]

Here is ilogic code to export materials from the active material library to excel.  It includes the material categories, and the appearance and physical assets.

Unfortunately, no logic yet to import the materials from excel.  Maybe someday, but no time now...

[terry.nicholls · Accepted solution]

Hi, I modified your code @Anonymous to include the physical properties of the materials as well. Thanks for that. Solved a problem for me.  

Sub Main()
	'This sub will export the categories and names of all materials and their appearance and physical assets 
	'in the active material library To an excel file.
'[	Variables
	Dim j As Integer	' Speadsheet data row counter
	j = 6				' Start data entry on row j, 
	'( row 1 is Title, 2 is Library Name, 3 is library internal name, 4 is library location, (path), 5 is headers)
']
'[	'Set reference to Inventor application
	Dim InvApp As Inventor.Application
	Dim AML As AssetLibrary
	Dim cat As AssetCategory
	Dim ma As MaterialAsset
	'Get the Inventor Application object.
	InvApp = ThisApplication
	'Get the Active Material Library
	AML = InvApp.ActiveMaterialLibrary
']   	  
'[	Set Excel file path and name
	myPath = ThisDoc.Path
	myFileName = iProperties.Value("project", "Part Number") & ".xlsx"
	path_and_name = myPath & "\" & myFileName
']
'[	Open Excel
	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = True
']
'[	Check for existing excel file Or create one if not existing.	
	If Dir(path_and_name) <> "" Then 'workbook exists, open it
		excelWorkbook = excelApp.Workbooks.Open(path_and_name)
		'set the first sheet active
		excelSheet = excelWorkbook.Worksheets(1).activate
		With excelApp
			'Clear existing data
			.Range("A1:E1000").delete	
		End With
	Else 'workbook does Not exist, create it
		excelWorkbook = excelApp.Workbooks.Add
		excelSheet = excelWorkbook.Worksheets(1).activate 
		excelWorkbook.SaveAs(path_and_name)
	End If
	excelWorkbook.Save()
']
'[	Excel Column Headers
	With excelApp
		.Range("a5").value = "Category Name"
		.Range("b5").Value = "Material Name"
		.Range("c5").Value = "Appearance Asset"
		.Range("d5").Value = "Density"
		.Range("e5").Value = "Thermal conductivity"
		.Range("f5").value = "Specific heat"
		.Range("g5").Value = "Thermal expansion coefficient"
		.Range("h5").Value = "Young modulus"
		.Range("i5").Value = "Poisson ratio"
		.Range("j5").Value = "Shear modulus"
		.Range("k5").Value = "Minimum yield stress"
		.Range("l5").Value = "Minimum tensile strength"
		
	End With
']	
'[
	'write spreadsheet title, library display name, internal name, and file path to excel
	With excelApp
		.Range("a1").value = "INVENTOR MATERIALS LIST"
		.Range("A2").value = "Library Display Name: "
		.Range("b2").value = AML.DisplayName
		.Range("a3").value = "Library Internal Name:  "
		.Range("b3").value = AML.InternalName
		.Range("A4").value = "Library Location:  "
		.Range("b4").value = AML.FullFileName
	End With
']
'[	'Write material library list to excel
	With excelApp
	'Iterate through material categories
		For Each cat In AML.MaterialAssetCategories
			.Range("a" & j).value = cat.DisplayName
			j= j + 1
			'Iterate through each material in the category
				For Each ma In cat.Assets
					.Range("b" & j).value = ma.DisplayName
					.Range("c" & j).value = ma.AppearanceAsset.DisplayName
					.Range("d" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Density").value
					.Range("e" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Thermal_conductivity").value
					.Range("f" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Specific_heat").value
					.Range("g" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Thermal_expansion_coefficient").value
					.Range("h" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Young_modulus").value
					.Range("i" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Poisson_ratio").value 
					.Range("j" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Shear_modulus").value
					.Range("k" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Minimum_yield_stress").value
					.Range("l" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Minimum_tensile_strength").value
	
					j = j + 1
				Next
				j = j + 1
		Next
	End With
']
'[	Save and Close Excel
	excelWorkbook.Save() 	
'	excelWorkbook.Close
'	excelApp.Quit
'	excelApp = Nothing
'	MessageBox.Show("Exported Data to Excel", "Data Exported to Excel")
']
End Sub

[guido_overath]

Nice!
Thanks

[omvcilindri]

Hi 

thanks for your job, i tested the rule but dont' work for all category.

where did I go wrong to use it?

can anyone help me?
I attach screen and generated file

[omvcilindri]

I forgot is it possible to add the keyword column?

thanks to all

 

[terry.nicholls]

Hi @omvcilindri 

It looks like it is getting to a material and having a problem. I added a try so that it will skip it and keep going. I'm not sure it will work because its not failing for me and I'm learning with coding but give this ago.

I added Keywords, our library doesn't use them so it came up blank values. hopefully that works for you.

Sub Main()
	'This sub will export the categories and names of all materials and their appearance and physical assets 
	'in the active material library To an excel file.
'[	Variables
	Dim j As Integer	' Speadsheet data row counter
	j = 6				' Start data entry on row j, 
	'( row 1 is Title, 2 is Library Name, 3 is library internal name, 4 is library location, (path), 5 is headers)
']
'[	'Set reference to Inventor application
	Dim InvApp As Inventor.Application
	Dim AML As AssetLibrary
	Dim cat As AssetCategory
	Dim ma As MaterialAsset
	'Get the Inventor Application object.
	InvApp = ThisApplication
	'Get the Active Material Library
	AML = InvApp.ActiveMaterialLibrary
']   	  
'[	Set Excel file path and name
	myPath = ThisDoc.Path
	myFileName = iProperties.Value("project", "Part Number") & ".xlsx"
	path_and_name = myPath & "\" & myFileName
']
'[	Open Excel
	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = True
']
'[	Check for existing excel file Or create one if not existing.	
	If Dir(path_and_name) <> "" Then 'workbook exists, open it
		excelWorkbook = excelApp.Workbooks.Open(path_and_name)
		'set the first sheet active
		excelSheet = excelWorkbook.Worksheets(1).activate
		With excelApp
			'Clear existing data
			.Range("A1:E1000").delete	
		End With
	Else 'workbook does Not exist, create it
		excelWorkbook = excelApp.Workbooks.Add
		excelSheet = excelWorkbook.Worksheets(1).activate 
		excelWorkbook.SaveAs(path_and_name)
	End If
	excelWorkbook.Save()
']
'[	Excel Column Headers
	With excelApp
		.Range("a5").value = "Category Name"
		.Range("b5").Value = "Material Name"
		.Range("c5").Value = "Appearance Asset"
		.Range("d5").Value = "Density"
		.Range("e5").Value = "Thermal conductivity"
		.Range("f5").value = "Specific heat"
		.Range("g5").Value = "Thermal expansion coefficient"
		.Range("h5").Value = "Young modulus"
		.Range("i5").Value = "Poisson ratio"
		.Range("j5").Value = "Shear modulus"
		.Range("k5").Value = "Minimum yield stress"
		.Range("l5").Value = "Minimum tensile strength"
		.Range("m5").Value = "Keywords"
		
	End With
']	
'[
	'write spreadsheet title, library display name, internal name, and file path to excel
	With excelApp
		.Range("a1").value = "INVENTOR MATERIALS LIST"
		.Range("A2").value = "Library Display Name: "
		.Range("b2").value = AML.DisplayName
		.Range("a3").value = "Library Internal Name:  "
		.Range("b3").value = AML.InternalName
		.Range("A4").value = "Library Location:  "
		.Range("b4").value = AML.FullFileName
	End With
']
'[	'Write material library list to excel
	With excelApp
	'Iterate through material categories
		For Each cat In AML.MaterialAssetCategories
			.Range("a" & j).value = cat.DisplayName
			j= j + 1
			'Iterate through each material in the category
				
			For Each ma In cat.Assets
					Try
						.Range("a" & j).value = ""
						.Range("b" & j).value = ma.DisplayName
						.Range("c" & j).value = ma.AppearanceAsset.DisplayName
						.Range("d" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Density").value
						.Range("e" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Thermal_conductivity").value
						.Range("f" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Specific_heat").value
						.Range("g" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Thermal_expansion_coefficient").value
						.Range("h" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Young_modulus").value
						.Range("i" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Poisson_ratio").value 
						.Range("j" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Shear_modulus").value
						.Range("k" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Minimum_yield_stress").value
						.Range("l" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Minimum_tensile_strength").value
						.Range("m" & j).value = ma.Item("physmat_Keywords").Value
						
					Catch
						Try
						.Range("a" & j).value = "Error occured in gathering information from this material."
						.Range("b" & j).value = ma.DisplayName
						Catch
						.Range("a" & j).value = "Error- A material was skipped"
						End Try				
					End Try
					
				j = j + 1
				Next

				j = j + 1
		Next
	End With
']
'[	Save and Close Excel
	excelWorkbook.Save() 	
'	excelWorkbook.Close
'	excelApp.Quit
'	excelApp = Nothing
'	MessageBox.Show("Exported Data to Excel", "Data Exported to Excel")
']
End Sub

[johnsonshiue]

Hi Folks,

Just to bring some awareness about this. There is an Inventor App for all subscribers, called Assets Helper. It can help you manage Material and Appearance Library more efficiently.

https://apps.autodesk.com/INVNTOR/en/Detail/Index?id=181553538167586712&appLang=en&os=Win64

Many thanks!

[omvcilindri]

WOOW

great job!

so i also found out which material with the problem.

[omvcilindri]

Thanks for the tip, I didn't know this app existed, but I'll try it.

[Anonymous]

Very nice, but what I, (we), really need is a tool to go the other direction, to read from an excel spreadsheet to a material library. This would be invaluable for building and managing custom materials.
Thanks
Tony

[diego_dorta]

thank you, I found this rule very helpful.
just a question: there is a way to make it work even if the library is not fully categorized?

thank you

[terry.nicholls]

Hi, 

I didn't notice it was skipping uncategorized materials. I didn't spend the time to work out how to include them just hacked at it to spit out a flat list, see below. hope that helps.

Sub Main()
	'This sub will export the categories and names of all materials and their appearance and physical assets 
	'in the active material library To an excel file.
'[	Variables
	Dim j As Integer	' Speadsheet data row counter
	j = 6				' Start data entry on row j, 
	'( row 1 is Title, 2 is Library Name, 3 is library internal name, 4 is library location, (path), 5 is headers)
']
'[	'Set reference to Inventor application
	Dim InvApp As Inventor.Application
	Dim AML As AssetLibrary
	Dim cat As AssetCategory
	Dim ma As MaterialAsset
	'Get the Inventor Application object.
	InvApp = ThisApplication
	'Get the Active Material Library
	AML = InvApp.ActiveMaterialLibrary
']   	  
'[	Set Excel file path and name
	myPath = ThisDoc.Path
	myFileName = iProperties.Value("project", "Part Number") & ".xlsx"
	path_and_name = myPath & "\" & myFileName
']
'[	Open Excel
	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = True
']
'[	Check for existing excel file Or create one if not existing.	
	If Dir(path_and_name) <> "" Then 'workbook exists, open it
		excelWorkbook = excelApp.Workbooks.Open(path_and_name)
		'set the first sheet active
		excelSheet = excelWorkbook.Worksheets(1).activate
		With excelApp
			'Clear existing data
			.Range("A1:E1000").delete	
		End With
	Else 'workbook does Not exist, create it
		excelWorkbook = excelApp.Workbooks.Add
		excelSheet = excelWorkbook.Worksheets(1).activate 
		excelWorkbook.SaveAs(path_and_name)
	End If
	excelWorkbook.Save()
']
'[	Excel Column Headers
	With excelApp
		.Range("a5").value = "Category Name"
		.Range("b5").Value = "Material Name"
		.Range("c5").Value = "Appearance Asset"
		.Range("d5").Value = "Density"
		.Range("e5").Value = "Thermal conductivity"
		.Range("f5").value = "Specific heat"
		.Range("g5").Value = "Thermal expansion coefficient"
		.Range("h5").Value = "Young modulus"
		.Range("i5").Value = "Poisson ratio"
		.Range("j5").Value = "Shear modulus"
		.Range("k5").Value = "Minimum yield stress"
		.Range("l5").Value = "Minimum tensile strength"
		.Range("m5").Value = "Keywords"
		
	End With
']	
'[
	'write spreadsheet title, library display name, internal name, and file path to excel
	With excelApp
		.Range("a1").value = "INVENTOR MATERIALS LIST"
		.Range("A2").value = "Library Display Name: "
		.Range("b2").value = AML.DisplayName
		.Range("a3").value = "Library Internal Name:  "
		.Range("b3").value = AML.InternalName
		.Range("A4").value = "Library Location:  "
		.Range("b4").value = AML.FullFileName
	End With
']
'[	'Write material library list to excel
	With excelApp
	'Iterate through material categories
'		For Each cat In AML.MaterialAssetCategories
'			.Range("a" & j).value = cat.DisplayName
'			j= j + 1
'			'Iterate through each material in the category
				
			For Each ma In AML.MaterialAssets 'cat.Assets
					Try
						.Range("a" & j).value = ma.CategoryName'""
						.Range("b" & j).value = ma.DisplayName
						.Range("c" & j).value = ma.AppearanceAsset.DisplayName
						.Range("d" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Density").value
						.Range("e" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Thermal_conductivity").value
						.Range("f" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Specific_heat").value
						.Range("g" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Thermal_expansion_coefficient").value
						.Range("h" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Young_modulus").value
						.Range("i" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Poisson_ratio").value 
						.Range("j" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Shear_modulus").value
						.Range("k" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Minimum_yield_stress").value
						.Range("l" & j).value = ma.PhysicalPropertiesAsset.Item("structural_Minimum_tensile_strength").value
						.Range("m" & j).value = ma.Item("physmat_Keywords").Value
						
					Catch
						Try
						.Range("a" & j).value = "Error occured in gathering information from this material."
						.Range("b" & j).value = ma.DisplayName
						Catch
						.Range("a" & j).value = "Error- A material was skipped"
						End Try				
					End Try
					
				j = j + 1
				Next

				'j = j + 1
		'Next
	End With
']
'[	Save and Close Excel
	excelWorkbook.Save() 	
'	excelWorkbook.Close
'	excelApp.Quit
'	excelApp = Nothing
'	MessageBox.Show("Exported Data to Excel", "Data Exported to Excel")
']
End Sub