Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks)

by Corion (Pope)
on Jul 26, 2007 at 11:20 UTC ( #628896=note: print w/ replies, xml ) Need Help??


in reply to Using Win32::OLE and Excel - Tips and Tricks

In addition to this very good introduction, here's my approach to automating things in Excel that I know how to do manually in Excel. These steps apply to any (Office) application that has a macro recorder:

  1. Start the Excel Macro Recorder
  2. Perform the desired action(s) manually, for example, delete a row or bolden text or create a pivot chart
  3. Stop the Excel Macro Recorder
  4. Inspect the generated VB code
  5. Check that the generated VB code does what you want
  6. Translate the generated VB code to Perl / Win32::OLE invocations

That's all there is to it and it sure beats poring over the documentation to find out the needed function names.

Similar lists exist at Re^3: Problems with Microsft's new Office 'XML' and Re: Convert word(.doc) file to html file, but as this appears to be a good starting page for people working with Win32::OLE, I figured to put it here as well.


Comment on Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks)
Get the row and column number
by Anonymous Monk on Aug 25, 2007 at 00:14 UTC
    I am trying to do this is my program. Open an Excel File Search for a value in the excel file When i find the value i want to know the location of that value. For example if my excel file has 3 values A1-->apple A2-->Orange A3-->Grapes When i search the file for orange..it should say that orange found and return me the value A2. I am not able to find the location. Please help

      Read the Excel object model documentation. For a cell (or Range) there likely is a function returning the coordinates of the upper left corner. Maybe it's Cell->Address or something containing R1C1 in its name.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://628896]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (7)
As of 2014-09-03 08:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (35 votes), past polls