Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
A.Acheson
in reply to: jonathan.victor

 The send key method did not work in my testing and I would be surprised it could work.  One method to avoid this is to just delete the exported file before you begin so no overwriting error appears. 

If IO.File.Exists(xlBOMPath) = True Then
		xlWb = xlApp.Workbooks.Open(xlBOMPath)
	End If

Another method  is to delete the sheet in the existing exported file prior to the export method copying the named sheet from the template to the exported file. The downside is any other sheet in the template gets copied over also multiple times. 

 

 

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports XL = Microsoft.Office.Interop.Excel
Sub Main

	Dim path_and_name As String = ThisDoc.PathAndFileName(False) ' without extension
	Dim partsListFile As String = path_and_name & ".xlsx"
	Dim sheetName As String = "LISTA MATERIAIS"
	
	DeleteWorkSheet(partsListFile, sheetName)
	
	'define oDoc
	oDoc = ThisDoc.Document

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

	 ' 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
	DESKTOP = System.Environment.GetFolderPath(Desktop)
	oOptions.Value("Template") = DESKTOP & "\TEMPLATE_ESTRUTURA.xlsx"
	 
	'specify the columns to export         
	'oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION"
	 
	'specify the start cell
	oOptions.Value("StartingCell") = "A1"
	 
	'specify the XLS tab name
	'here the file name is used
	oOptions.Value("TableName") = sheetName 


	'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
	
	' export the Partslist to Excel with options
	oPartslist.Export(path_and_name & ".xlsx", _
	PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
End Sub

Sub DeleteWorkSheet(partsListFile As String ,sheetName As String)

	Dim xlApp As XL.Application = CreateObject("Excel.Application")
	Dim xlWb As XL.Workbook
	Dim xlWs As XL.Worksheet
	
	'In order to not show Excel
	xlApp.Visible = True 
	xlApp.DisplayAlerts = False
	If IO.File.Exists(partsListFile) = True Then
		
		xlWb = xlApp.Workbooks.Open(partsListFile)
		
		For Each xlWs In xlWb.Worksheets
			If xlWs.Name = sheetName Then
				xlWs.Delete
			End If
		Next
	End If

	xlWb.Save
	xlWb.Close(False)
	xlApp.DisplayAlerts = True
	xlApp.Quit
End Sub

 

If this solved a problem, please click (accept) as solution.‌‌‌‌
Or if this helped you, please, click (like)‌‌
Regards
Alan