excel pasting

toni_t_banire@gsk.com has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to copy data from one spreadsheet to another. The data in spreadheet A is derived from a formula so I want SpreadsheetB to copy just the values and not drag along the formula with it.

I set up a macro which I am now trying to convert into perl

Range("N10").Select
Selection.Copy
Range("G13").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I am ok with everything except or line 4.

Below is my code:

$excel = CreateObject OLE "Excel.Application" || die "CreateObject : $
+!\n";
$excel -> {Visible}=1;
$excelfile2 = $excel -> Workbooks -> Open("c:\\sheetA.xls");
$excelfile = $excel -> Workbooks -> Open("c:\\sheetB.xls");
$xls4 = $excelfile -> Worksheets(1);
$xls5 = $excelfile2 -> Worksheets(1);


$xls5 -> Range ("H58:H105") -> Copy;
$xls4 -> Cells (9, 15) -> {PasteSpecial} ="Paste=xlValues, Operation=x
+lNone, SkipBlanks=False, Transpose=False";
$xls4 -> Cells (9, 14) -> Select;
[download]

Everything works ok except for the Paste = xlValues. The data is pasted in the right column put it still shows the link to SheetA.xls rather than just the value.

I need to do things this way as I have to send SheetB.xls to peole who wont have a copy of SheetA.xls and I don't want excel to warn about links to another spreadheet when SheetB.xls is opened

Can someone pleeeassssssseeeeeee HELP me

Toni

Edited by footpad, ~Thu Nov 29 05:38:14 2001 (GMT)

Comment on excel pasting Download Code

Replies are listed 'Best First'.
Re: excel pasting by c-era (Curate) on Nov 28, 2001 at 22:16 UTC
Two things. First, use Win32::OLE::Const to import the excel constants. Second, PasteSpecial is a method, not a property. The code below should work (yes it has been tested). `use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; $excel = Win32::OLE->new ("Excel.Application") \|\| die "CreateObject : +$!\n"; $excel -> {Visible}=1; $excelfile2 = $excel -> Workbooks -> Open("c:\\sheetA.xls"); $excelfile = $excel -> Workbooks -> Open("c:\\sheetB.xls"); $xls4 = $excelfile -> Worksheets(1); $xls5 = $excelfile2 -> Worksheets(1); $xls5 -> Range ("H58:H105") -> Copy; $xls4 -> Cells (9, 15) -> PasteSpecial (xlPasteValues); $xls4 -> Cells (9, 14) -> Select;` [download] If you have any additional questions, feel free to ask.	[reply] [d/l]
Re: Re: excel pasting by toni_t_banire@gsk.com (Initiate) on Nov 29, 2001 at 18:06 UTC
This is to say a really big THANK YOU you to c-era for resolving my excel paste problem. Really appreciate the quick response Toni	[reply]
Re: excel pasting by osfameron (Hermit) on Nov 28, 2001 at 21:49 UTC
I think you need to write this as a method call, with the data as a hash, for example: (Please note I've not tested this - see next section also) `$xls4->Cells(9, 15)-> PasteSpecial( { Paste => xlValues, Operation => xlNone, SkipBlanks=> False, Transpose => False", });` [download] This won't work, because the constants xlValues, xlNone and False aren't imported using Win32::OLE. I think there's a way to find these... need to `perldoc Win32::OLE` a bit more, but a way of checking is to go to the VBA screen's 'Immediate' window and type `print xlValues` (which is `-4163` apparently...) Sorry if this is vague - I'll test more later if I have time! Cheerio! Osfameron	[reply] [d/l] [select]
Re: excel pasting by Anonymous Monk on Jun 28, 2016 at 09:51 UTC
Need help i can't Paste Special values. For me this solution doesn't work. Please Help	[reply]
Re^2: excel pasting by marto (Cardinal) on Jun 28, 2016 at 09:59 UTC
The post you've replied to is over a decade old. Consider posting a new question and providing the details needed to help you. Read and understand How do I post a question effectively?.	[reply]


XP is just a number
	PerlMonks