Using optional parameters with excel add sheets

I’m learning how to program excel with Xojo.

I was able to set a windows VM, remote debugger, office and I have an example code like this:

[code]Dim excel As New ExcelApplication
excel.Workbooks.Add
excel.visible = True

excel.ActiveWorkbook.Sheets.add //Add a new worksheet
excel.ActiveWorkbook.Sheets.add //Add another new worksheet

//Select another sheet and make it active
excel.ActiveWorkbook.Worksheets(2).Activate
excel.Range(“A1”).Value = “Some random data on the second sheet”
excel = Nil

Exception err as OLEException
MsgBox err.message
[/code]
This code add 2 sheets before the first sheet, activate the second sheet and put something on A1.

Then I read this, and I want to know if it is possible to use the Parameters listed there in Xojo. I did some tests trying to find a way but was not successful. Also searched of the answer and got nothing.

Let’s say I want to add 2 sheets (3 total) and the resulting workbook display Hoja1, Hoja2 and Hoja3 instead of Hoja3, Hoja2 and Hoja1, without parameters I can do this:

excel.ActiveWorkbook.Sheets.add //Add a new worksheet excel.ActiveWorkbook.Sheets.add //Add another new worksheet excel.Worksheets("Hoja1").Move(excel.Worksheets("Hoja3")) //Move Hoja1 before Hoja3 - Hoja1, Hoja3, Hoja2 excel.Worksheets("Hoja2").Move(excel.Worksheets("Hoja3")) //Move Hoja2 before Hoja3 - Hoja1, Hoja2, Hoja3

Is it possible to do something like:

excel.ActiveWorkbook.Sheets.add.after("Hoja1").count(2)

[quote=379084:@Alberto De Poo]Is it possible to do something like:
excel.ActiveWorkbook.Sheets.add.after(“Hoja1”).count(2)[/quote]

Hello Alberto,

Yes, it should be able to do as you said, and I can’t get it to work. An alternative method is to sort them alphabetically to change from: Hoja3, Hoja2, Hoja1 to Hoja1, Hoja2, Hoja3. Here is example code which works on my Windows 10 machine:

[code]Sub Action() Handles Action
Dim excel As New ExcelApplication
excel.Workbooks.Add
excel.visible = True

excel.ActiveWorkbook.Sheets.add //Add a new worksheet
excel.ActiveWorkbook.Sheets.add //Add another new worksheet
excel.ActiveWorkbook.Sheets.add //Add another new worksheet

//Sort sheets in alphabetical order
//Changes from Sheet3, Sheet2, Sheet1 to Sheet1, Sheet2, Sheet3
Dim i, j as Integer
For i = 1 to excel.Application.Sheets.Count-1
For j = 1 to excel.Application.Sheets.Count-1
If StrComp(excel.Application.Sheets(j+1).Name, excel.Application.Sheets(j).Name, REALbasic.StrCompLexical) <0 Then
excel.Application.Sheets(j+1).Move(excel.Application.Sheets(j))
End If
Next j
Next i

Exception err as OLEException
MsgBox err.message
End Sub
[/code]

For some reason, an empty parameter of nil is not recognized with the Xojo Plugin Add function for Before.

Is this workaround good enough for you?

Thank you Eugene, yes that works to sort the Sheets.

I could modify that if I ever need to sort 10 or more, but I don’t think that’s needed.

Do you know if there is a Feedback case with a request to use the extra Parameters with Add and other functions?

BTW I’m reading your book about Excel 2016 and Xojo, what is the best way to contact you with minor details/comments? The email listed on it, private message in this forum or other way?

Your welcome :slight_smile:

I don’t know of a feedback case for this issue. Feel free to submit one, or I can add one for you.

You can contact me either by email or the forum, both are good. Thanks for your helpful suggestions!