Re^2: Excel Macro Writting Issue

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.

Comment on Re^2: Excel Macro Writting Issue

Replies are listed 'Best First'.
Re^3: Excel Macro Writting Issue by dasgar (Priest) on Aug 26, 2010 at 03:50 UTC
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]

Replies are listed 'Best First'.

Re^3: Excel Macro Writting Issue
by dasgar (Priest) on Aug 26, 2010 at 03:50 UTC

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.

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]