Custom BOM export

[doug_schaefer]

My iLogic code works just how I want it to when there is only 1 sheet. When there are multiple sheets I get this error

"SaveAs method of Workbook class failed". My code can call the sheet by it's number or by calling the first sheet, which is where the BOM is. Tried the code both ways same result.  I'm hoping there's a simple fix, I've spent way to much time on this as is. Here's my code:

'-------------Start Of ilogic ------------------------------------------------
'get the path and name of the drawing file
path_and_name = ThisDoc.PathAndFileName(False) ' without extension

THEWAY=ThisDoc.Path 'for the filename formating

'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
oOptions.Value("Template") = "C:\Users\dschaefe\Desktop\PROJECT-TB\BOM TEMPLATES\CLOROX BOM 2020.xlsx"
'specify the columns to export 
oOptions.Value("ExportedColumns") = "ITEM QTY;PART NUMBER;DESCRIPTION;VENDOR"
'specify the start cell
oOptions.Value("StartingCell") = "B18"
'specify the XLS tab name
'here the file name is used 
oOptions.Value("TableName") = "CLOROX BOM"
'choose to include the parts list title row
oOptions.Value("IncludeTitle") = False
'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = False

'export the Partslist to Excel with options
oPartslist.Export(THEWAY & "\" & iProperties.Value("Project", "Part Number") & "R" & iProperties.Value("Project", "Revision Number") & "_" & iProperties.Value("Project", "Description") & ".xlsx", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)



'-------------End of ilogic ------------------------------------------------