Office Automation: Excel, adding sheets

[Goofus828]

I am embarrassed to admit but I do not know what "Variant" means in this statement

oExcel.WorkSheets.Add( [Before as Variant], [After as Variant], [Count as Variant]....

I want to add a worksheet after the one I already have.

Is the Before a logical? or an Excel constant?

Thanks!

 

[JackTheC]

Can't you record a macro when performing a manual worksheet addition and then look at the recorded macro?
(I have no recent excel version)

 

[Mike Gagnon]

oExcel = CREATEOBJECT("excel.application")
oWorkbook = oExcel.Workbooks.Add()
oWorkbook.Sheets.Add
oSheet = oWorkbook.ActiveSheet
oSheet.Move(,oWorkbook.Sheets(4)) && Move after sheet3
oSheet.Move(oWorkbook.Sheets(4),) && Move before sheet3
oExcel.Visible =.t.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JRB-Bldr]

Depending on what you might want to do once the new Sheet has been added, you might want to look at some of the code in the FAQ:

Excel - How to do Automation from VFP
faq184-4428​

Good Luck,
JRB-Bldr

 

[Mike Lewis]

I am embarrassed to admit but I do not know what "Variant" means in this statement
...
Is the Before a logical? or an Excel constant?

You shouldn't be embarrassed. It's not a concept that is familiar to VFP programmers.

The short answer is that a variant is the equivalent of any VFP data type - it varies. I know that doesn't help. So what you've got to do is to look at the Help for each specific case.

In the case of Sheets.Add, the Help says that the "Before" parameter is "An object that specifies the sheet before which the new sheet is added". So the data type is an object reference.

So, suppose you want to place the new worksheet before the active sheet, you could do this:

oActive = oWorkbook.ActiveSheet
oWorkbook.Sheets.Add(oActive)

Similarly for the "After" parameter.

If you don't care where the new sheet is inserted, just leave the parameters blank.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads