Adding a worksheet after existing worksheet in excel automation

[ravicoder]

Hi all

I am using excel automation in most of my applications.

My query is : how to add a worksheet after existing worksheets in an active workbook

using the worksheet.add method adds a worksheet before existing worksheets not after
e.g. for a report based on monthly values, if I want to add worksheet named june 2024 after worksheet named May 2024, worksheets.add method adds the new worksheet before May 2024 but I want it to come after may 2024.

worksheet.add(after:=(pv wksheet name)) throws a syntax error

Any help / sample code would be welcome

thanks in advance

 

[Chris Miller]

You try to use the named parameter syntax obj.meth(parametername>= value), which does only work in VBA, not in VFP.

So you have to use the parameter in their defined ordinal position. According to

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

The After parameter is the second parameter.

It must be the object, not the name of a sheet.

Chriss

 

[Mike Lewis]

You need something like this (in VFP, not VBA):

[tt]oWB.Sheets.Add(null, oWB.ActiveSheet)[/tt]

where oWB is an object reference to the workbook (which you already have in the code that you posted).

As Chris mentioned, the parameters have to be passed according to their position in the parameter list, not by their name (as in VBA). The "after" parameter is the second parameter to the Add method, so we have to pass a NULL as the first parameter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ravicoder]

Great .. Thanks Mike. That worked !!

I wrote the code as below

oSheet = oActiveWorkbook.Worksheets(cmmyy)
oActiveWorkbook.Worksheets.Add(null,oSheet)

cmmyy is the name of the current sheet

Thanks again
Ravi

 

[Mike Lewis]

Good to see it worked, Ravi. Thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

oSheet = oActiveWorkbook.Worksheets(cmmyy)
oActiveWorkbook.Worksheets.Add(null,oSheet)

can be shortened to

oActiveWorkbook.Worksheets.Add(null,oActiveWorkbook.ActiveSheet)

Not that it is much simpler or faster.

But as Mike said the strtaight forward way to get at the active Worksheet is using the ActiveSheet property of the active workbook. If you wouldn't have oActiveWorkbook but your Excel automation object is in oExcel, that would be oExcel.ActiveWorkbook.ActiveSheet.

Chriss