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
SB said:
I open an Excel workbook ... and insert a new sheet.
Therefore you can use something like this:
Code:
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:
Code:
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:
Code:
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
