Move to last sheet - Excel Automation in Visual Foxpro 1

[sw1202]

Good afternoon,
I open an Excel workbook (which already has several worksheets in it) from Visual Foxpro and insert a new sheet. The new sheet is always inserted before the last sheet and I need that new sheet to be the last one. I tried running macros in Excel to see the codes, but it always refers to the sheet name (for example: "Financial") which will change as we go along.
Does anybody have a solution to this? Please help.
Thank you.

SW

 

[GriffMG]

There is a numeric way to traverse sheets...

FOR I = 1 to OEXCEL.WORKSHEETS.COUNT
  OEXCEL.WORKSHEETS(I).ACTIVATE
NEXT

So, you should be able to get the last sheet with:

OEXCEL.WORKSHEETS(OEXCEL.WORKSHEETS.COUNT).ACTIVATE

I think you could then move it to BEFORE the previous sheet:

OEXCEL.WORKSHEETS(OEXCEL.WORKSHEETS.COUNT).Move(OEXCEL.WORKSHEETS.COUNT-1)

Not tested it I'm afraid.
Would need to be wary of having just one sheet - that wouldn't work at all.
Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TamarGranor]

The Sheets.Add method accepts a couple of parameters to indicate where the new sheet should be place. Take a look at the docs.

https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.add

Tamar

 

[GriffMG]

Clever, I didn't think the OP had control over the adding, or why would he/she ask?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Griff,

when you try Add() with wrong parameters you can get an OLE error 1004 "The Add-Property of the Shedet Object cannot be assigned".
You have to play close attention to the docs, they require the Before/After parameters to be sheet objects, not just the index number.

This is also true for the Move() method.

So to add a new sheet after the currently last sheet (which is what you get when you use Excels new sheet button at the end of the sheet tabs. The Workbook.Sheets.Add() method used without any parameters puts the new sheet before the last one, it seems. Who thought this was a good default...

Anyway, you could work with this in either case.

SW, you said

I open an Excel workbook ... and insert a new sheet.

Therefore you can use something like this:

oExcel= CreaateObject("Excel.Application")
oWB = oEcel.Workbook.Open(...)
oLastSheet = oWB.Sheets(oWB.Sheets.Count)
oNewSheet = oWB.Sheets.Add(,oLastSheet)
...

This Add call leaves out the optional Before sheet object and specifies the sheet after which the new sheet should be added. That's what the documentation says Tamar pointed to. It makes no sense to specify both parameters, they are optional, which means you can skip them. But only as I do here, by not specifying a value for the first "before" parameter, I literally pass in nothing, then the comma before "oLastSheet" is no joke, that actually makes the first parameter nothing. This nothing has still too be in first place of the parameterization. If you'd skipped the comma and wrote Add(oLastSheet) that would go in as first parameter and mean you add the sheet before the last sheet. So skipping parameters does not mean leaving them out fully, it means instead of writing the value to pass in you write a comma to get to the next parameter. In VBA you have named parameters and could do something like Sheets.Add(Before=oLastSheet), but in VFP you simply provide parameters in order of documentation or intellisense. and skipping them means go on to the next parameter with a comma.

If you already have some sheet, you can move it, as Griff indicated. He also said he didn't test and I don't blame him, I afdten also son't. What's important here, too, is that you need to specify the object itself, not just its index number.

So the solution for moving one of the existing sheets to last position:

oExcel= CreaateObject("Excel.Application")
oWB = oEcel.Workbook.Open(...)
oSheetToMove = oWB.Sheets(oWB.Sheets.Count-1)
oLastSheet = oWB.Sheets(oWB.Sheets.Count)
oSheetToMove.Move(,oLastSheet)
...

Again, notice, the docs say the parameterization is Move(Before, After,....). Both Before and After are optional, if you want to say After you have to start with a comma to specify the After parameter. And just by the way: obviously, you cannot specify both parameters at the same time.

So there may be your difficulty in understanding how to use these optional parameters from VFP. By the way, this also is a way to use parameter skipping, when you want to let VFP automate an already open excel session, using GetObject() only specifying the second parameter, not the first:

oExcel= GetObject(,"Excel.Application")

Which throws an error, if no Excel session is running and has the other disadvantage of not knowing which excel session you get, if Excel runs multiple times already. But this was just to demonstrate this is also the way to skip parameters within VFP functions. It's a VFP thing to do it that way, and in some situations it can even lead to code having multiple commas in a row. Intellissne is helpful here when it knows what object you deal with. That comes up, by the way, if you first write code in the command window line by line. In a code editor you would need to specify LOCAL Excel as Excel.Application, oWB as Excel.Workbook, etc to get the intellisense guiding your coding. And then you also only get parameter described as "Before as Variant" and "After as Variant" and the "Variant" type is not helpful. So sometimes you still either have to know their meaning, guess them correct or look into the docs.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

Thank you Olaf, I was not aware of that

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Griff,

no harm done. Move still points in the right direction and the docs are there. But even I had to fail with index number first. It's just a pitty this causes a misleading error description. At first I thought VFP failed with OLE automation here, as the error message describes "Add" as a property while it works without parameters. But then this should ring the bell it would be strange if you could only call Add() (or Move) without parameterization.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[sw1202]

Wow, thank you all very much for your help. I am going to try it again and report back.
Happy Friday!

SW

 

[sw1202]

oExcel= CreaateObject("Excel.Application")
oWB = oEcel.Workbook.Open(...)
oLastSheet = oWB.Sheets(oWB.Sheets.Count)
oNewSheet = oWB.Sheets.Add(,oLastSheet)

The above codes works beautifully. Below is how I used it:
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
MESSAGEBOX('Having problem starting Excel. Please contact your software developer.',0)
return .F.
endif
oExcel.visible = .T.

gcTable=GETFILE('XLSX', 'Select or Create a .XLSX:', 'Select', 1, 'Select or Create')
v_path = JUSTPATH(gcTable)+'\'

oWorkbook = oExcel.Workbooks.Open(gcTable)

oLastSheet = oWorkbook.Sheets(oWorkbook.Sheets.Count)
oNewSheet = oWorkbook.Sheets.Add(,oLastSheet)
oActiveSheet = oNewSheet


Again, thank you all so very much. I can't say enough how much I appreciate tektips community!
Have a nice weekend,

SW