Excel 2016 export

HCL Notes/Domino 8.5 Forum (includes Notes Traveler)

Subject: Excel 2016 export

Feedback Type: Problem

Product Area: Database

Technical Area: Application Development

Platform: Windows

Release: 8.5.3

Reproducible: -Reproducibility-

I have recently upgraded from office 2007 to Office 2102. The export I had that creates 4 worksheets in a spreadsheet and opens it within the code no longer works. The xls opens, the first worksheet gets named, but it crashes with OLE: Automation object error.
It does not seem to want to create the second worksheet.

Any help would be greatly welcomed.
I have searched but cannot find reference to object change for excel 2012.

This sub is the starting point
Sub OutputToExcel (objReport As ReportDetails )
'script about to end
Dim xlApp As Variant

Dim xlsheet As Variant
Dim xlsheet2 As Variant
Dim xlsheet3 As Variant
Dim xlsheet4 As Variant
Dim xlsheet5 As Variant

Dim strName1 As String
Dim strName2 As String
Dim strName3 As String
Dim strName4 As String

Dim totalquotes As Integer
Dim rows As Integer, cols As Integer

'initiailise our local variables
rows = 1
cols = 1
'start the excel application.
Set xlApp = CreateObject("Excel.Application.16")
xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add

xlApp.ReferenceStyle = 1
xlApp.Range("A1").Select
'create the sheet headings
strName1 = objReport.WsLabels + " Summary"
strName2 = objReport.WsLabels + " Order Details"
strName3 = objReport.WsLabels+ " Sales Leads"
strName4 = objReport.WsLabels + " Payroll"

Call CreateSheets(xlApp, strName1, strName2, strName3, strName4)
xlApp.StatusBar = "Importing Notes Data into workbook. Please be patient..."
'add the engineers to sheet 1

Call objReport.SetUpSheet2 ( xlApp ) ' so we know how many rows are used for summary sheet
Call objReport.SetUpSheet1 ( xlApp, strName2, strName3 )
Call objReport.SetUpSheet3 ( xlApp )
Call objReport.SetUpSheet4 ( xlApp, strName1 )

With xlApp.Worksheets(1)
.PageSetup.Orientation = 2
.PageSetup.centerheader = "Field Service Incentive Scheme Report"
.Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
.Pagesetup.CenterFooter = ""
End With
'tell the user that we have finished
xlApp.StatusBar = "Importing Field Service Quote data is Complete."
xlApp.Worksheets( strName1 ).Activate
xlApp.Range("H3").Select

End Sub

Feedback number WEBBAH9LPH created by ~Ethan Cisvelumanli on 01/03/2017

Status: Open
Comments: Sorry the Excel version is 2016