Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Inserting copied rows in Excel using Win32::OLE

by agroman (Initiate)
on Aug 28, 2004 at 18:28 UTC ( #386633=perlquestion: print w/replies, xml ) Need Help??

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

I'm trying to get the following VB code to run with Win32::OLE.
Rows("1:1").Select Selection.Copy Rows("2:2").Select Selection.Insert Shift:=xlDown
Here's code I'm currently trying to use and the errors that are returned.
my $Workbook = $Excel->Workbooks->Open({Filename=>"test.xls"}); my $curSheet = $Workbook->Worksheets(1); $curSheet->Rows("1:1")->Select; $curSheet->Rows("1:1")->Copy; $curSheet->Rows("2:2")->Select; $curSheet->Rows("2:2")->Insert(Shift => 'xlDown'); OLE exception from "Microsoft Office Excel": Unable to get the Insert property of the Range class Win32::OLE(0.1701) error 0x800a03ec in METHOD/PROPERTYGET "Insert" at C:\ line 6
I've tried several different ways of trying to call the Insert function, with no success. What am I doing wrong?

Replies are listed 'Best First'.
Re: Inserting copied rows in Excel using Win32::OLE
by bmann (Priest) on Aug 29, 2004 at 00:53 UTC
    Passing the parameters to Insert as an anonymous hashref (as mentioned in Re: Inserting copied rows in Excel using Win32::OLE) is half the battle.

    The second problem is quoting 'xlDown'. You don't show where xlDown is defined in your code. If you are defining it as a Constant or importing it via Win32::OLE::Const then drop the quotes and it will work as expected. If you aren't defining xlDown in your script, you'll need to use whatever number xlDown means to Excel.

    Personally, I'd import the constants from Excel's typelib with use Win32::OLE::Const 'Microsoft Excel';.

Re: Inserting copied rows in Excel using Win32::OLE
by jmcnamara (Monsignor) on Aug 29, 2004 at 00:10 UTC

    The correct syntax should probably be (note the anonymous hash ref):
    $curSheet->Rows("2:2")->Insert({Shift => 'xlDown'});
    However, I've tried this and it doesn't work. I don't know why, perhaps xlDown is superfluous in the context of a row range.

    As a workaround, if you use the Insert() method without parameters it will give the desired effect of shifting the rows down:


    Update: bmann got it below, ++. In addition to the hashref xlDown is an imported constant and shouldn't be quoted. The following works:
    $curSheet->Rows("2:2")->Insert({Shift => xlDown});


Re: Inserting copied rows in Excel using Win32::OLE
by guha (Priest) on Aug 29, 2004 at 00:01 UTC

    It is not entirely clear to me what you are trying to achieve. Your use of the Copy method and node title indicates that you want PasteSpecial instead of Insert which just inserts a new empty row before the Range.

    Furthermore the value of the Shift property should really be xlShiftDown.

    It is not possible from your example to infer how you initialize your Application object and how the Excel constants are imported.

    Also I think that the argument of the Rows method should be


    Anyway HTH somewhat.

      Rows("1:1").Select Selection.Copy Rows("2:2").Select Selection.Insert Shift:=xlDown
      This code was generated from a macro I recorded in excel. It selects Row1, copy's it, selects Row2, and finally pastes the copied Row1 into Row2 shifting all the other rows down (right click, select Insert Copied Cells). For an example, imagine that the spreadsheet looks like this :
      1: Cell1, Cell2, Cell3 2: Data1, Data2, Data3 3: Foo01, Foo02, Foo03
      After the macro runs you will have :
      1: Cell1, Cell2, Cell3 2: Cell1, Cell2, Cell3 3: Data1, Data2, Data3 4: Foo01, Foo02, Foo03
      This is what I am trying to achieve. In addition, I feel like I'm working blind. Does anyone know of a guide to OLE and how to use it with perl? The Win32::OLE docs are great, but I'm having a hard time figuring out how to use all of the objects that I find in the Visual Basic Object View and the ActiveState OLE-Browser.

        Well as I see it, the real problem is that the Excel/Word/etc/etc object model is quite complex. Lots of objects, methods and properties to sling around. To my experience there are no shortcuts but to understand that model.

        That isn't easily done of course, and you have to start somewhere and the tools you mentioned are the maps into this exciting world.

        You will start the baby-talk, that is translate macros to Perl and get things done. Often you'll do this mechanically and perhaps not really understand what's going on. However as your path goes on, more problems to solve, you will gain more experience and at some time you will begin to "understand" the thinking behind the model and you will start to know where to look for methods and properties in this vast object forrest, you will see the light.

        Now this is when the fun starts and the power is under your fingertips.

        You asked for more information, there is one document in the Tutorials section, namely Brother cacharbe's famous Excel, Using Win32::OLE and Excel - Tips and Tricks, node that I recommend you to read and ponder.


Re: Inserting copied rows in Excel using Win32::OLE
by doowah2004 (Monk) on Aug 31, 2004 at 15:16 UTC
    What you want to use to copy and paste into excel is:
    my $Workbook = $Excel->Workbooks->Open({Filename=>"test.xls"}); my $curSheet = $Workbook->Worksheets(1); $curSheet->Range("2:2")->{Value} = $curSheet->Range("1:1")->{Value +};

    Hope that this helps.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://386633]
Approved by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (4)
As of 2022-05-23 16:55 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (82 votes). Check out past polls.