in reply to Excel Macro Writting Issue

Well, that's a start on the code. However, folks are still left with having to guess about your code. For example, I'm presuming based on the syntax that you're using Win32::OLE rather than Spreadsheet::WriteExcel. Knowing which module you're using could help us figure out what may be the problem. (By the way, if you're using Win32::OLE, I'd personally argue that you don't need the macros since Perl can do the work instead. That's just my opinion though.)

Also, in your OP, you're referring to 'macros', but it looks like in your code that your really working with formulas. There is a difference between the two.

In the code that you provided, I believe your not providing correct data to the Range function in the next to the last line. That's where I'd start looking. However, since you didn't provide any more code, no one can tell what is actually being passed in.

I'm not trying to give you hard time. Just want to show you how the lack of code is really preventing others from providing help. As you can see above, I've got some thoughts about where to start looking, but I don't have enough info about your code to even give you any useful help.

Replies are listed 'Best First'.
Re^2: Excel Macro Writting Issue
by sachin_p_ganjare (Initiate) on Aug 25, 2010 at 05:31 UTC

    Thanks dasgar. You are correct, I am working on formulas & using WIN32::OLE.

    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.

    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.

    But problem is ,first sheet gets populated correctly, but for next sheets it skips updation of many cells.

    But when I debug the same code using breakpoints at the code bits I have given, everything runs fine.

    What I am thinking is when executing in normal mode, before the column is updated with formulas, perl goes for the execution of next code line. I tried using wait, sleep functions, but to no avail.

    (:-( I am sorry about the limited code sharing due to restrictions I have to abide by. Hope you all understand.

[reply]

           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.

[reply]

In Section Seekers of Perl Wisdom