how can I Copy a worksheet in excel? 2

[Simonbegg]

Hello all,
Trying to copy a sheet within a book.
The excel VBA code for this is :

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)

the bit I'm struggling to translate to VFP is the:
Before:=Sheets(1)

so can anyone tell me what I should out after:
oexcel.Sheets("Sheet1").Copy

Cheers
Simon

 

[StewartUK]

Hi Simon,

What version of VFP are you using?

The syntax would look something like

oexcel.Sheets("Sheet1").Copy("Sheet2")

Here's an extract from the help:

expression.Copy(Before, After)

expression Required. An expression that returns an object in the Applies To list.

Before Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

After Optional Variant. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

So the code above should <g> copy the sheet and put it before sheet2.


Hope that helps,

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

 

[Simonbegg]

Stewart,
Version 7.


Had tried that,
get the error
"OLE IDispatch exception Code 0 from Excel. unable to get the copy property of the worksheet class."

Cheers
Simon

 

[StewartUK]

Simon,

Working from the command window, I got the following code to work:

oxl = GETOBJECT(,'excel.application')
obk = oxl.ActiveWorkbook
obk.Sheets(1).Copy(obk.Sheets(3))

Hope that helps,

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

 

[Simonbegg]

Cheers Stewart,
works a treat!

 

[mkrausnick]

expression.Copy(Before, After)

expression Required. An expression that returns an object in the Applies To list.

Before Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

After Optional Variant. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

Stewart:
Where can I find the help file from which you extracted this?

Thanks.

Mike Krausnick
Dublin, California

 

[Mike Gagnon]

I see a reference to it here:

http://msdn.microsoft.com/library/d.../en-us/vbaxl11/html/xlmthCopy1_HV03088280.asp

You can also search in your computer for the VBAXL help file, you will find the same reference to the Copy method (Please note that all this is Visual Basic code) :
C:\Program Files\Microsoft Office\OFFICE11\1033\VBAXL10.CHM



Mike Gagnon

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

 

[mkrausnick]

OK, found it, thanks. The CHM and the MSDN both give the same information about copying AFTER, but my VB to VFP universal translator isn't working, so I can't translate their example:

This example copies Sheet1, placing the copy after Sheet3.

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

into VFP. Stewart's code above:

oBook = oExcel.ActiveWorkbook
oBook.Sheets(p_LastMonth).Copy(oBook.Sheets(p_LastMonth))

works, but places the new worksheet BEFORE the specified sheet. Keying off the "expression.Copy(Before, After)" specification, I tried:

oBook.Sheets(p_LastMonth).Copy(oBook.Sheets([COLOR=red][b] , [/b][/color]p_LastMonth))

but of course got "Invalid number of parameters". I also tried different variations of:

oBook.Sheets(p_LastMonth).Copy("After=oBook.Sheets(p_LastMonth)")

to no avail. Do you have an idea how I might be able to copy a sheet AFTER another sheet?

Mike Krausnick
Dublin, California

 

[Mike Gagnon]

Take a look at faq184-4266 (the last example) shows you how to move sheets before and after other sheets.


Mike Gagnon

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

 

[StewartUK]

Sorry Mike,

I'm in England and had gone home yesterday before I was alerted to your message. I actually got to the help by going through Excel to the VB editor (Alt+F11 in Excel) and then to the object browser (F2). Then you can click on one of the PEMs and press F1.

I think you can do the same through VFP's object browser but I've never done it that way.

Looks like you're in safe hands!

Stewart

Hope that helps,

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

 

[mkrausnick]

Thanks Mike for replying.

I tried the example in the FAQ and it appears to not work as indicated.

I changed the reference from Sheet3 to Sheet2
(i.e., oSheet.Move(,oWorkbook.Sheets(2)) && Move after sheet2 and
oSheet.Move(oWorkbook.Sheets(2),) && Move before sheet2).

This way I can see the before/after action inside the list of sheet tabs as opposed to being on one end or the other.

First, running the example with both the final two statements commented out causes Sheet4 to appear before Sheet1.

Second, running the example with the final line commented out causes the sheet tabs to appear in this order:
Sheet1, Sheet4, Sheet2, Sheet3.

Third, running the example with the second-to-final line commented out again causes Sheet4 to appear before Sheet1, indicating perhaps that this format is not valid, although it did not throw an error.

This was tested using Office 2003 and VFP 9 running on WinXP SP2. Maybe the behavior changed since the FAQ was written.

I can work around it by placing my Grand Totals sheet at the end and inserting before that. It would still be nice to know how to place a sheet after another sheet though.

Anyway, thanks for the help.


Mike Krausnick
Dublin, California

 

[Mike Gagnon]

Mike
I'm not sure I understand the problem.
Using the following I get the following results:

oEx = CREATEOBJECT('excel.application')
oBook = oEx.Workbooks.Add()
oSheet = oBook.Activesheet && This being sheet "1"
oSheet.Move(obook.Sheets(3),)  && Move sheet "1" and place it before sheet "3"

oEx = CREATEOBJECT('excel.application')
oBook = oEx.Workbooks.Add()
oSheet = oBook.Activesheet  && Sheet 1
oSheet.Move(,obook.Sheets(3)) && Move sheet 1 after sheet 3
oEx.Visible = .t.

VFP9.0, Excel 2003, Windows XP.





Mike Gagnon

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

 

[mkrausnick]

Interesting. I get the same results you get using your code. But the (slightly modified) code in the FAQ, which looks identical to me, produces a different result:

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

The other relevent point is that I am copying a sheet rather than moving. So I changed 'Move' to 'Copy' in your code posted in this thread and got the expected result - the copy appears after Sheet3 ?!?

Then I went back to my the app I'm working on and added the leading comma, and lo and behold, this time it worked. I think I'm ready for the funny farm.

Thanks for sticking with me. That deserves a star!





Mike Krausnick
Dublin, California

 

[mkrausnick]

Stewart - I neglected to acknowlege your response also. Thanks for the Alt-F11 tip. The VFP Object Browser does not appear to have help for the Excel COM library, but I can access the help in Excel via Alt-F11.

Mike Krausnick
Dublin, California

 

[Mike Gagnon]

Mike

Thank you for the star, glad it worked out.


Mike Gagnon

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