Excel OLE Copy and Insert

damianpadden has asked for the wisdom of the Perl Monks concerning the following question:

I have a spreadsheet that i need to copy columns D:I and insert them back into the spreadsheet at column D. I have just started to use the ole function and have been ok so far but i am stuck on this part. Any help would be great Cheers Damo

Comment on Excel OLE Copy and Insert

Replies are listed 'Best First'.
Re: Excel OLE Copy and Insert by maa (Pilgrim) on Mar 24, 2004 at 12:48 UTC
Hi, damianpadden If you have Excel installed the easiest way to learn the VBA syntax is to actually record a macro (Tools/Macros/) in Excel and then read the code (Alt+F11)... I'm assuming that your problem is, in fact, copying the data from Book1.xls/Sheet1!$D:$I into Book2.xls/Sheet1!$D or simply from one sheet to another... the VB would look like this: `Columns("D:I").Select Selection.Copy Sheets("Sheet2").Select Range("D1").Select ActiveSheet.Paste` [download] With the exception of ActiveSheet.Paste this should be easily converted to Win32::OLE syntax. `#With simplifications... #Assume you already have a $ExcelAp, $Book and $Sheet1 & 2 object/vari +able set up $Sheet1->Columns("D:I")->Copy(); $Sheet2->Range("D1")->Select(); $Sheet2->Paste();` [download] HTH - Mark	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Excel OLE Copy and Insert
by maa (Pilgrim) on Mar 24, 2004 at 12:48 UTC

Hi, damianpadden

If you have Excel installed the easiest way to learn the VBA syntax is to actually record a macro (Tools/Macros/) in Excel and then read the code (Alt+F11)...

I'm assuming that your problem is, in fact, copying the data from Book1.xls/Sheet1!$D:$I into Book2.xls/Sheet1!$D or simply from one sheet to another... the VB would look like this:

    Columns("D:I").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D1").Select
    ActiveSheet.Paste
[download]

With the exception of ActiveSheet.Paste this should be easily converted to Win32::OLE syntax.

#With simplifications...
#Assume you already have a $ExcelAp, $Book and $Sheet1 & 2 object/vari
+able set up
$Sheet1->Columns("D:I")->Copy();
$Sheet2->Range("D1")->Select();
$Sheet2->Paste();
[download]

HTH - Mark

[reply]
[d/l]
[select]