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

  1. Range("N10").Select
  2. Selection.Copy
  3. Range("G13").Select
  4. 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)

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]
      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]

      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]

Back to Seekers of Perl Wisdom