excel spreadsheet tab order

This forum is for eXpress++ general support.
Post Reply
Message
Author
Dian
Posts: 21
Joined: Tue Feb 02, 2010 8:36 am
Location: Midland Texas

excel spreadsheet tab order

#1 Post 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.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: excel spreadsheet tab order

#2 Post 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.
The eXpress train is coming - and it has more cars.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: excel spreadsheet tab order

#3 Post 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.
The eXpress train is coming - and it has more cars.

User avatar
Auge_Ohr
Posts: 1405
Joined: Wed Feb 24, 2010 3:44 pm

Re: excel spreadsheet tab order

#4 Post 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 ...
greetings by OHR
Jimmy

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: excel spreadsheet tab order

#5 Post 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 )
The eXpress train is coming - and it has more cars.

Post Reply