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
[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.
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?
