Re^2: Inserting copied rows in Excel using Win32::OLE

in reply to Re: Inserting copied rows in Excel using Win32::OLE
in thread Inserting copied rows in Excel using Win32::OLE

Rows("1:1").Select
Selection.Copy
Rows("2:2").Select
Selection.Insert Shift:=xlDown
[download]

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

After the macro runs you will have :

1: Cell1, Cell2, Cell3
2: Cell1, Cell2, Cell3
3: Data1, Data2, Data3
4: Foo01, Foo02, Foo03
[download]

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.

Comment on Re^2: Inserting copied rows in Excel using Win32::OLE Select or Download Code

Replies are listed 'Best First'.
Re^3: Inserting copied rows in Excel using Win32::OLE by guha (Priest) on Aug 29, 2004 at 10:44 UTC
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. HTH	[reply]

Replies are listed 'Best First'.

Re^3: Inserting copied rows in Excel using Win32::OLE
by guha (Priest) on Aug 29, 2004 at 10:44 UTC

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.

HTH

[reply]

In Section Seekers of Perl Wisdom