We have built code to export information from a database to an Excel workbook. It works fine for EXCEL 2007 but fails when the user has EXCEL 2013. on building the automation object. Here is the section of code that fails when the 2nd worksheet is being set. (dpsheet). Does anyone have a possible workaround?
Dim application As Variant
Dim sheet As Variant
Dim dpSheet As Variant
Dim dpaSheet As Variant
Set application = CreateObject(“Excel.Application”)
application.Visible = True
application.Workbooks.Add
application.Workbooks(1).Activate
Set sheet = application.Workbooks(1).Worksheets(1)
If achoice = “All Reports” Or achoice = “Out of Date” Then
Set sheet = application.Workbooks(1).ActiveSheet
sheet.Name = “Overdue”
If achoice = “All Reports” Then
Set dpSheet = application.Workbooks(1).Worksheets(2)
dpSheet.Name = “Approval Started”
Set dpaSheet = application.Workbooks(1).Worksheets(3)
dpaSheet.Name = “All Docs”
Subject: Creating multiple Worksheets in Excel 2013 using Lotus Script
Finally got this to work - have to add sheet to workbook differently because Excel 2013 starts with only one worksheet in a workbook by default instead of 3. Because I have users that have earlier versions of Excel as well I have to check on version as well. Excel 2007 is Version 12.0 Excel 2013 is Version 15.0.
Set application = CreateObject(“Excel.Application”)
appver = application.version
application.Visible = True
If application.version <= “12.0” Then
application.Workbooks.Add
application.Workbooks(1).Activate
else
Set workbook = application.Workbooks.Add
workbook.activate
Set colSheets = Workbook.Sheets
colSheets.Add ,2
End If
Set sheet = application.Workbooks(1).Worksheets(1)
If achoice = “All Reports” Or achoice = “Out of Date” Then
Set sheet = application.Workbooks(1).ActiveSheet
sheet.Name = “Overdue”
Set dpSheet = application.Workbooks(1).Worksheets(2)
Set dpaSheet = application.Workbooks(1).Worksheets(3)
dpSheet.Name = “Approval Started”
dpaSheet.Name = “All Docs”
Haven’t tried this yet, but I believe Excel 2013 only gives you one worksheet by default, so instead of just switching to application.Workbooks(1).Worksheets(2) you’ll need to create the worksheets first. Should be something like