Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
Any help would be much appreciated
Paul Newton
On 16/02/2017 16:48, Paul Newton wrote:
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
I've had fun with this in the past. I found a way that works by only having one workbook open at a time. You open the one you want to copy into first. Then open the one to copy from, select the data and copy, then close it, then paste into the other one.
Here's some chopped up code. Think I had to tinker with it to get it to work with office 2013 but this is the general idea. I couldn't get it to work with both workbooks open. I'm sure it can be done but...
* start automation and open file etc.
objexcel.Workbooks.add objexcel.sheets.add * open sheet and paste onto here. objexcel.workbooks.open(filetocopyfrom)
objexcel.range("A2").select * select full area objexcel.Range("a1").CurrentRegion.Select objexcel.selection.copy * now close this so it should select the first one. objexcel.ActiveWorkbook.Close * need to select first book to paste into objexcel.workbooks(1).activate objexcel.range("A1").select * and paste in objexcel.ActiveSheet.paste * add new tab for next one etc..
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office:17-19 Foley Street, London W1W 6DW Tel:0207 299 7960
Thanks Peter - I'll play around with that
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: 16 February 2017 17:09 To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
On 16/02/2017 16:48, Paul Newton wrote:
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
I've had fun with this in the past. I found a way that works by only having one workbook open at a time. You open the one you want to copy into first. Then open the one to copy from, select the data and copy, then close it, then paste into the other one.
Here's some chopped up code. Think I had to tinker with it to get it to work with office 2013 but this is the general idea. I couldn't get it to work with both workbooks open. I'm sure it can be done but...
* start automation and open file etc.
objexcel.Workbooks.add objexcel.sheets.add * open sheet and paste onto here. objexcel.workbooks.open(filetocopyfrom)
objexcel.range("A2").select * select full area objexcel.Range("a1").CurrentRegion.Select objexcel.selection.copy * now close this so it should select the first one. objexcel.ActiveWorkbook.Close * need to select first book to paste into objexcel.workbooks(1).activate objexcel.range("A1").select * and paste in objexcel.ActiveSheet.paste * add new tab for next one etc..
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office:17-19 Foley Street, London W1W 6DW Tel:0207 299 7960
[excessive quoting removed by server]
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
Hey rk - Great program name!!! Gosh - U must be bored - and its not even a Friday... :-)
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 1:04 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
It's not boredom; it's avoidance of real work. :-)
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Thursday, February 16, 2017 1:26 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Hey rk - Great program name!!! Gosh - U must be bored - and its not even a Friday... :-)
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 1:04 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
Ha ha - I totally hear you! Gotta admit - I'm doing a bit of the same thing today!!!
;-)
-K-
On 2/16/2017 1:34 PM, Richard Kaye wrote:
It's not boredom; it's avoidance of real work. :-)
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: Thursday, February 16, 2017 1:26 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Hey rk - Great program name!!! Gosh - U must be bored - and its not even a Friday... :-)
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 1:04 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
- Program: FunWithExcel1.prg
- Date: 02/16/2017 12:46
- VFP Version: Visual FoxPro 09.00.0000.7423 for Windows
- Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet
ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
[excessive quoting removed by server]
Thanks Richard - you've given me some further ideas
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 16 February 2017 18:04 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
You're welcome, Paul.
I just played around in the command window with the Excel object til it worked. The beauty and power of VFP...
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Friday, February 17, 2017 3:46 AM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Thanks Richard - you've given me some further ideas
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 16 February 2017 18:04 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
Richard
Unfortunately, you are copying the worksheet to a new worksheet in the same workbook. I want to copy the active worksheet to a new worksheet in a different workbook (which already exists), _without_ opening the target workbook ... still don't have a solution for that
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 17 February 2017 12:14 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
You're welcome, Paul.
I just played around in the command window with the Excel object til it worked. The beauty and power of VFP...
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Friday, February 17, 2017 3:46 AM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Thanks Richard - you've given me some further ideas
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 16 February 2017 18:04 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
On 17/02/2017 12:35, Paul Newton wrote:
Richard
Unfortunately, you are copying the worksheet to a new worksheet in the same workbook. I want to copy the active worksheet to a new worksheet in a different workbook (which already exists), _without_ opening the target workbook ... still don't have a solution for that
Without opening the target workbook? Can't see how you would achieve that. When you open the workbook and add a sheet you then need excel to save it for you and to update the internal structure of the workbook.
Peter
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office:17-19 Foley Street, London W1W 6DW Tel:0207 299 7960
But I believe you can do it in an excel macro .. at least I've found sample code that does just that - looks like I WILL have to open the target workbook
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Peter Cushing Sent: 17 February 2017 12:40 To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
On 17/02/2017 12:35, Paul Newton wrote:
Richard
Unfortunately, you are copying the worksheet to a new worksheet in the same workbook. I want to copy the active worksheet to a new worksheet in a different workbook (which already exists), _without_ opening the target workbook ... still don't have a solution for that
Without opening the target workbook? Can't see how you would achieve that. When you open the workbook and add a sheet you then need excel to save it for you and to update the internal structure of the workbook.
Peter
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office:17-19 Foley Street, London W1W 6DW Tel:0207 299 7960
[excessive quoting removed by server]
But that's not "the OOP" way, that's a hack, and you probably can do it accessing the files at low level, but will be to less costly using the OOP way.
That's the problem with objects, yo need to instantiate them before using :-)
El 17/2/2017 13:35, "Paul Newton" Paul.Newton@pegasus.co.uk escribió:
Richard
Unfortunately, you are copying the worksheet to a new worksheet in the same workbook. I want to copy the active worksheet to a new worksheet in a different workbook (which already exists), _without_ opening the target workbook ... still don't have a solution for that
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 17 February 2017 12:14 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
You're welcome, Paul.
I just played around in the command window with the Excel object til it worked. The beauty and power of VFP...
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Friday, February 17, 2017 3:46 AM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Thanks Richard - you've given me some further ideas
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 16 February 2017 18:04 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
- Program: FunWithExcel1.prg
- Date: 02/16/2017 12:46
- VFP Version: Visual FoxPro 09.00.0000.7423 for Windows
- Notes: open a workbook, copies the 2nd sheet before the current 2nd
sheet, makes another copy after the 3rd sheet
ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet.
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
- select all
- copy
- open another workbook
- paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
My little prg was a proof of concept; not a bespoke solution to your conundrum. :-)
If you just call the Copy method with no params it actually opens a new workbook.
Use the command line and have a play. It's your best friend. It should be easy enough to make it do what you want to do. Use ox.Workbooks.Open() to open your target workbook. Set an object ref to that.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Friday, February 17, 2017 7:35 AM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Richard
Unfortunately, you are copying the worksheet to a new worksheet in the same workbook. I want to copy the active worksheet to a new worksheet in a different workbook (which already exists), _without_ opening the target workbook ... still don't have a solution for that
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 17 February 2017 12:14 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
You're welcome, Paul.
I just played around in the command window with the Excel object til it worked. The beauty and power of VFP...
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Paul Newton Sent: Friday, February 17, 2017 3:46 AM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Thanks Richard - you've given me some further ideas
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 16 February 2017 18:04 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
Clearly I'm avoiding doing anything productive right now.
************************************** * Program: FunWithExcel1.prg * Date: 02/16/2017 12:46 * VFP Version: Visual FoxPro 09.00.0000.7423 for Windows * Notes: open a workbook, copies the 2nd sheet before the current 2nd sheet, makes another copy after the 3rd sheet ************************************** ACTIVATE SCREEN CLEAR LOCAL m.ox AS Excel.Application, m.ow AS Excel.Workbook, m.oss AS Excel.Worksheet m.ox=CREATEOBJECT([excel.application]) m.ox.Workbooks.Open([c:\rfc\copytest.xlsx]) m.ow=m.ox.ActiveWorkbook ?m.ow.Sheets.Count m.oss=ow.Sheets(2) && this example has 2 sheets. get a ref to the 2nd sheet m.oss.copy(ow.Sheets(2)) && copy it before the 2nd sheet ?m.ow.Sheets.Count m.oss.copy(,ow.Sheets(3)) && copy it after the 3rd sheet ?m.ow.Sheets.Count m.ox.Visible=.t.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, February 16, 2017 12:36 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
Thanks Richard - I'll try that too
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: 16 February 2017 17:36 To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
ox=CREATEOBJECT([excel.application]) ox.Visible=.t. ox.Workbooks.Open(GETFILE([xlsx])) ow=ox.ActiveWorkbook ow.SaveCopyAs([c:\rfc\copytest.xlsx]) ox.Workbooks.Open([c:\rfc\copytest.xlsx])
This copies the entire document, not an individual sheet. --
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Jan Bucek Sent: Thursday, February 16, 2017 12:12 PM To: profoxtech@leafe.com Subject: Re: Copy Excel worksheet to another workbook
Dne 16.2.2017 v 17:48 Paul Newton napsal(a):
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
And what about a little different way?
1. select all 2. copy 3. open another workbook 4. paste
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
I hadn't seen anyone mention this, but this class library may be helpful. Greg Greene was attempting to manipulate XLSX files without the need for Excel at all.
https://vfpx.codeplex.com/wikipage?title=XLSXWorkbook&referringTitle=Hom...
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Paul Newton Sent: Thursday, February 16, 2017 11:48 AM To: profox@leafe.com Subject: Copy Excel worksheet to another workbook
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
I've been using it for a while. It's pretty easy to use and he's got some good samples in his docs. I primarily use it for exporting a table/cursor to Excel. I have not used any of the reading from Excel methods yet. One caveat is it is not the fasting thing in the world once you start getting into tables with thousands of rows.
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kevin J Cully Sent: Friday, February 17, 2017 2:14 PM To: profoxtech@leafe.com Subject: RE: Copy Excel worksheet to another workbook
I hadn't seen anyone mention this, but this class library may be helpful. Greg Greene was attempting to manipulate XLSX files without the need for Excel at all.
https://vfpx.codeplex.com/wikipage?title=XLSXWorkbook&referringTitle=Hom...
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Paul Newton Sent: Thursday, February 16, 2017 11:48 AM To: profox@leafe.com Subject: Copy Excel worksheet to another workbook
Hi all
I want to use automation to copy the active worksheet into another workbook (as an additional worksheet) I believe I need something like this ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets("Sheet3") But I can't find how to send the correct way to do it in VFP
o = CreateObject("Excel.Application") o.Visible = .t. o.Workbooks.Open("C:\O32\OperaNext\Opera32\DEMODATA\SQSLACL.XLS") o.ActiveSheet.Copy(What to put here?)
Any help would be much appreciated
Paul Newton
[excessive quoting removed by server]
-
Fernando D. Bozzo -
Jan Bucek -
Kevin J Cully -
Kurt at VR-FX -
Kurt Wendt -
Paul Newton -
Peter Cushing -
Richard Kaye