What I have done is, I have created a macro in excel first. Then by editing that macro, I have extracted VB code. And this VB code I have translated into perl using WIN32::OLE.

Sounds like you've got the right idea. One commentary note. Personally, I like to skip translation on the select and activate stuff. Just seems like unnecessary extra steps to me.

     I have three different formulas to be pasted in specific column in three different worksheets. I am doing it one by one in loop. First I am pasting a formula in a cell. Then I am copying that cell & pasting it in cells below.

So my question here is why do you feel the need to 'copy and paste' with this task? If you have the formula in a variable, you should be able to just insert that into all 3 cells. The only advantage of 'cut and paste' is that Excel will update the formula for cells specified as 'B1' format in the formula while leaving '$B$1' formatted cell references as is.

     $Sheet->Paste

I think that this line may be the culprit. You copied a formula from a cell and trying to paste it into a worksheet. Instead, you need to paste into a cell.

     I am sorry about the limited code sharing due to restrictions I have to abide by.

There's a lot of good reason for not sharing all of your code. However, if you can try to provide some code that similar enough to your code that someone can run it and reproduce the errors/problems, you'll get much better help.

In reply to Re^3: Excel Macro Writting Issue by dasgar
in thread Excel Macro Writting Issue by sachin_p_ganjare

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.