excel spreadsheet tab order

[Dian]

I am using DC_Array2Excel() to create 3 tabs spreadsheet. It creates different sheet order depending on what Excel version you are on. For Excel 2013 and up, it creates sheet tab in this order: Sheet3, Sheet2, Sheet1. And in Excel 2010 it creates in the correct order Sheet1, Sheet2, Sheet3. I need it to always create sheet in the correct order sheet1, sheet2, sheet3 regardless of Excel version. This is the array I pass in {{aGeneral},{aForm10},{aForm11}}. I need General to always be on the first 1st sheet1, Form 10 to always be 2nd sheet2, and Form 11 to be 3rd sheet3. Do you have any command that I can use to fix this problem? Thank you.

[rdonnay]

I am going to need to be able to read the Excel version.
Hopefully that can be done from the ActiveX properties.
I will look into this.

[rdonnay]

When I run 2010, the version returned by the Excel ActiveX is 14.0.
When I run 2016, the version returned by the Excel ActiveX is 16.0
I don't have 2013, but my guess would be that the version is 15.0

To get the version do this:

oExcel := CreateObject("Excel.Application")

? oExcel:version

When building your array of sheets you could build them in reverse order if the version is 15 or later.

[Auge_Ohr]

I need General to always be on the first 1st sheet1, Form 10 to always be 2nd sheet2, and Form 11 to be 3rd sheet3. Do you have any command that I can use to fix this problem? Thank you.

the Problem is not inside DC_Array2Excel()*** ... it will fill Sheet as you pass multi-dim Array
*** c:\exp20\Source\Dclipx\_dcfunct.prg

if M$ change Order of Sheet Name or use Name which you do not like : just rename it

Code: Select all

IF Excel2013  
// change Sheet Name
   oExcel:Application:Worksheets(3):activate()
   oSheet := oExcel:ActiveSheet
   oSheet:name := "Sheet 1"

   oExcel:Application:Worksheets(1):activate()
   oSheet := oExcel:ActiveSheet
   oSheet:name := "Sheet 3"

so activate() Sheet you want and than rename it ...

[rdonnay]

If the sheet captions need to be changed, this can be done with the 12th parameter:

FUNCTION DC_Array2Excel( cExcelFile, aData, nOrientation, lDisplayAlerts, ;
lVisible, lAutoFit, lTrimNilColumns, lCombineSheets, ;
cPassword, lFreezeRow1, lCSVFallBack, aSheetCaptions )