Export from inventor to a excel with macros

[david_alcalde6P6BN]

Hi everyone,

I have a ilogic code in inventor witch creates an excel .xlsx for the bill of materials based on a template .xlsm 

for some reason it does not create an .xlsm and the outputed xlsx does not have any of the macros from the template (not even turned off, it just doesn't have the macros).

Is there anyway to make the newly made excel file be a .xlsm and have the macros working in the file?

Right now, after creating the file I am opening both files, copying the macros to the new one, saving it as a xlsm, and running the macros afterwards, but it's terrible when you have to do it all the time.

[bradeneuropeArthur]

could you share the code, so that we can modify?

[david_alcalde6P6BN]

Here it is @bradeneuropeArthur  (it obviously makes several other things as well....)

Dim drawing As DrawingDocument = ThisDoc.Document


Dim oDoc As Document = ThisApplication.ActiveDocument

Dim PRIMERA_LINEA As String
    PRIMERA_LINEA = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("PRIMERA LINEA").Value)
	
Dim SEGUNDA_LINEA As String
    SEGUNDA_LINEA = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("SEGUNDA LINEA").Value)
	
Dim DISEÑADOR As String
    DISEÑADOR = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("DISEÑADOR").Value)
	
Dim NUMERO_MODULO As String
    NUMERO_MODULO = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("NUMERO MODULO").Value)
	
Dim CODIGO_TECNICO As String
    CODIGO_TECNICO = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("CODIGO TECNICO").Value)
	
Dim TIPO_DE_PLANO As String
    TIPO_DE_PLANO = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("TIPO DE PLANO").Value)
	
Dim NUMERO_PLANO As String
    NUMERO_PLANO = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("NUMERO PLANO").Value)
	
Dim NUMERO_INSTALACION As String
    NUMERO_INSTALACION = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("NUMERO INSTALACION").Value)
	
Dim CODIGO_DE_PARTICION As String
    CODIGO_DE_PARTICION = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("CODIGO DE PARTICION").Value)
	
Dim NOMBRE_DE_CLIENTE As String
    NOMBRE_DE_CLIENTE = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("NOMBRE DE CLIENTE").Value)
	
Dim TIPO_DE_INSTALACION As String
    TIPO_DE_INSTALACION = CStr(oDoc.PropertySets.Item("Inventor User Defined Properties").Item("TIPO DE INSTALACION").Value)
	
	
Dim oPList As PartsList = Nothing
For Each oList As PartsList In oDoc.ActiveSheet.PartsLists
    If oList.Title IsNot Nothing Then
        oPList = oList
        Exit For
    End If
Next

If oPList Is Nothing Then
    MsgBox("No hay una lista de materiales en la hoja activa.", MessageBoxIcon.Exclamation, "No hay lista de materiales")
    Return
GoTo es
End If

Dim partsList As PartsList = drawing.ActiveSheet.PartsLists(1)
Dim bomFileName = ThisDoc.PathAndFileName(False) & ".xlsx"
Dim Template = "C:\Users\Public\VAULT\Inventor Libraries\Templates\223500_Template.xlsm"

'____________________________________________

drawing = ThisApplication.ActiveDocument

'If Err.Number <> 0 Then
'	MsgBox("No existe una lista de materiales en el dibujo." & vbCrLf & vbCrLf & "Debes crear una lista de materiales antes de ejecutar la BOM.", MessageBoxIcon.Exclamation, "No hay lista de materiales") 
'	GoTo fin
'End If

If ThisDoc.PathAndFileName() = Nothing Then
	MsgBox ("Debes guardar el documento primero.",MessageBoxIcon.Exclamation,"No existe el fichero")
	GoTo fin
End If

Excelapp = CreateObject("Excel.application")
Excelapp.visible = False
Excelapp.DisplayAlerts = False

excelworkbook = Excelapp.workbooks.add(Template)
'excelworkbook = Excelapp.worksheets("DatosD").delete
'excelworkbook = Excelapp.worksheets.add("DatosD")

Dim bomNameChanged As String = ThisDoc.FileName(False)
bomNameChanged = bomNameChanged.Replace("X", "N")
bomNameChanged = bomNameChanged.Replace("Z", "N")
bomNameChanged = bomNameChanged.Replace("P", "N")
bomNameChanged = bomNameChanged.Replace("x", "N")
bomNameChanged = bomNameChanged.Replace("z", "N")
bomNameChanged = bomNameChanged.Replace("p", "N")
bomNameChanged = bomNameChanged.Replace("n", "N")

Dim bomNameChangedTwice As String = bomNameChanged & ".xlsx"

Dim bomFinalName As String = String.Concat(ThisDoc.Path, "\", bomNameChangedTwice)

excelworkbook.saveas(bomFinalName)
excelworkbook.close
Excelapp.quit

'--------------------------------------------


'____________________________________________


'Options
'Name				Value Type	Valid For export formats
'-------------------------------------------------------------
'TableName			String		kMicrosoftExcel, kMicrosoftAccess 
'ExportedColumns	String		containing semicolon separated column titles All 
'IncludeTitle		Boolean		kMicrosoftExcel, kTextFileCommaDelimited, kTextFileTabDelimited, kUnicodeTextFileCommaDelimited, kUnicodeTextFileTabDelimited 
'StartingCell		String		kMicrosoftExcel 
'Template			String		kMicrosoftExcel 
'AutoFitColumnWidth	Boolean		kMicrosoftExcel 



Dim options As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap()
options.Value("TableName") = "DatosD"
options.Value("StartingCell") = "A1"
'options.Value("Template") = bomNewName

folderName = ThisDoc.Path
partsList.Export(bomFinalName, PartsListFileFormatEnum.kMicrosoftExcel, options)

'Add additional info

Dim fileName As String = ThisDoc.FileName(False)
fileName = fileName.Replace("X", "N")
fileName = fileName.Replace("Z", "N")
fileName = fileName.Replace("P", "N")
fileName = fileName.Replace("x", "N")
fileName = fileName.Replace("z", "N")
fileName = fileName.Replace("p", "N")
fileName = fileName.Replace("n", "N")


GoExcel.Open(bomFinalName, "BOM")
'GoExcel.CellValue("H5") = fileName
GoExcel.CellValue("F4") = PRIMERA_LINEA
GoExcel.CellValue("F5") = SEGUNDA_LINEA
GoExcel.CellValue("K3") = DateTime.Now.Date
GoExcel.CellValue("K4") = DISEÑADOR
'GoExcel.CellValue("M3") = NUMERO_MODULO
'GoExcel.CellValue("N3") = CODIGO_TECNICO & "-" & TIPO_DE_PLANO & "-" & NUMERO_PLANO
'GoExcel.CellValue("F2") = NUMERO_INSTALACION & "-" & CODIGO_DE_PARTICION
GoExcel.CellValue("F3") = NOMBRE_DE_CLIENTE & " - " & TIPO_DE_INSTALACION




GoExcel.Save
GoExcel.Close
'-----------------------------------------------------------


'-----------------------------------------------------------

go = MessageBox.Show("¿Quiere abrir el archivo?", FileName(False) & ".xlsx", MessageBoxButtons.YesNo)
If go = 6 Then ThisDoc.Launch(bomFinalName)
	GoTo fin
	
	
	es :

MsgBox("No existe una lista de materiales en el dibujo." & vbCrLf & vbCrLf & "Debes crear una lista de materiales antes de ejecutar la BOM.", MessageBoxIcon.Exclamation, "No hay lista de materiales") 
GoTo fin
	
	fin :

[bradeneuropeArthur · Accepted solution]

use this method instead:

excelworkbook = Excelapp.workbooks.open(Template)

[david_alcalde6P6BN]

Thanks, @bradeneuropeArthur  it works.

[bradeneuropeArthur]

@david_alcalde6P6BN 

You know the difference between .add and .open now?

[david_alcalde6P6BN]

Not really @bradeneuropeArthur , I can see what open does now, but I have no clue about add
I have very little experience writing ilogic, and all I hope is to get things done, but always happy to learn, so, can you tell me the difference?

(my coding experience with ilogic implies basically writing something that seems right putting a dot behind it and choosing beetween options, then try and error until it works or I surrender)

[bradeneuropeArthur · Accepted solution]

With add you add an empty workbook
with open you open a template as you needed.

[david_alcalde6P6BN]

@bradeneuropeArthur  thanks for the explanation