Page 1 of 1
excel spreadsheet tab order
Posted: Fri Aug 25, 2017 2:06 pm
by 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.
Re: excel spreadsheet tab order
Posted: Mon Aug 28, 2017 11:17 am
by 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.
Re: excel spreadsheet tab order
Posted: Mon Aug 28, 2017 11:43 am
by 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.
Re: excel spreadsheet tab order
Posted: Mon Aug 28, 2017 3:40 pm
by Auge_Ohr
Dian wrote: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 ...
Re: excel spreadsheet tab order
Posted: Tue Aug 29, 2017 7:57 am
by 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 )