04-18-2023
08:43 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
04-18-2023
08:43 PM
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
Or if this helped you, please, click (like)
Regards
Alan