sachin_p_ganjare has asked for the wisdom of the Perl Monks concerning the following question:

I am inserting a macro in a particular column, across three different worksheets in excel workbook. This is being done in a loop. In first sheet all the macros are inserted properly, but for next sheets, only few of the cells gets updated with macro.

The strange thing is when I debug the same program with stepwise execution, everything is disturbingly fine.

Monks please help.

UPDATES!!! The code bit

$active_cell->{FormulaR1C1} = $my_macro_expression;
$active_cell->Select;
$active_cell->Copy;
$Sheet->Range($active_cell, $end_cell)->Select;
$Sheet->Paste

[download]

UPDATES!!! The solution

while using different worksheets, script was not activating them before writting formula. Thats it.

UPDATES!!! Solution leads to another question

How the hell script was working fine in debugging mode without worksheet activation command????

Replies are listed 'Best First'.
Re: Excel Macro Writting Issue
by roboticus (Chancellor) on Aug 24, 2010 at 20:39 UTC

    sachin_p_ganjare:

    Hmmm ... I can't see any problems in the code you've provided. Perhaps the problem is somewhere in the code you didn't show....

    ...roboticus

[reply]
Re: Excel Macro Writing Issue
by davies (Monsignor) on Aug 24, 2010 at 22:54 UTC
    OK, I'll bite. It's late at night and I'm waiting for the bridge results to come out, so I'll try to turn my telepathy mode on. Excel has two ways of inserting rows and columns, even from the keyboard. If you try Insert | Cells (or its equivalent in 2007 or later), you will get the Excel menu that shows what I mean. The two modes are Entire Row/Column and what I call "Bill Gates Knows Best" mode. It sounds to me as if you want entire columns but are using BGKB. Your code should look something like $shtList->Range('zEndList')->EntireColumn->Insert;. This I have taken from another of my nodes, Re^3: Win32::Ole excel external data range, where I show the inserting of rows among other things.

    Roboticus is right, though. You would get much better help if you showed the minimum code necessary to demonstrate the problem.

    Regards,

    John Davies
[reply]
[d/l]
Re: Excel Macro Writting Issue
by dasgar (Priest) on Aug 25, 2010 at 04:20 UTC

    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.

[reply]

      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]

Back to Seekers of Perl Wisdom