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]