I've been having a bad time, so if I come across as crusty and unpleasant, please forgive me. But there are so many things wrong with your question that I was very tempted to ignore it altogether. Then I saw the answers about regexes and replacements, and decided that I might one day have to sort out this mess and that it would be better to start now.
Forget regexes and using Perl for replacements.
I'm going to assume that this isn't your mess and that you're some poor so-and-so who has been given a "wonderful opportunity". The first thing you need to do is learn Excel. I can't really advise on how to do that, as I'm too advanced, but there are plenty of sources on the net. The second thing is to learn some Excel style. Raffensperger's guidelines are something I recommend to a lot of people. I don't agree with everything he says, but I'd have fewer problems if no-one deviated. Your deviations (that are obvious from what you write) are:
Column A should not contain anything addable. It should contain text describing what the line contains, or in the case of a database download, the database index or description for a small table. But adding it to anything should not make sense. Ditto row one. I would forgive you if there were any sign of the use of boundary rows and columns, but this is something only a few people with scars do (although it's very valuable).
You are creating an unholy mishmash of data. Your formula in C6 refers to cells A1:A3. Why isn't it in column A? Or if it must be in C, why not row 1 or 3?
The formula you give us, =A1+A2+A3, is plain evil. What would happen if someone inserted a row? =sum(A1:A3) would have all sorts of advantages.
The next thing you need to do is to understand how to ask a question. A few references are How (Not) To Ask A Question and and I know what I mean. Why don't you?, both of which have references of their own. I strongly suspect you have at least an XY Problem, and quite possibly a chain that goes back to some letter before A :-). Based on what you write, I wouldn't even bet that Perl is to tool for this job - if you know enough Excel to automate it, why not use VBA? But if Perl is the right tool, you should Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks), remembering Excel’s Select and Activate considered harmful. I've been hoping for a quip to come up saying something like "clear questions and runnable code get the best answer". Your code doesn't run and your question isn't clear. Do you want the C6 formula copied to D7 (what your question says) or D6 (what your code apparently tries to do)? I am going to assume the former, as it's the more difficult case.
Time for me to stop taking my problems out on you and give you what you want.
The solution to this problem isn't regexes, it's a copyable formula. First, replace that monstrosity in C6 with =sum(A$1:A$3). Since you appear not to know Excel, I will risk teaching you to suck eggs by explaining that the dollar signs make a reference absolute. They can be placed before the letter, the number or both. When this is copied, the column will be relative but the row absolute. This will give you the staggering effect you want. Now, getting the formulae into this pretty state is not trivial and does involve iteration and regexes, but that may not be necessary depending on the actual problem, which we don't know.
A point on programming style: it is almost invariably bad style to have "magic numbers" or other hard coded information in your code. The sheet number and the ranges are examples of this. Excel has a very powerful feature of naming ranges, which can be essential to writing understandable, maintainable code, whether in Perl or VBA. And they can be dynamic. A second point is that to name a variable "$Data" is not helpful. No-one will expect a variable to contain anything but data.
The second part of the code snippet you provide doesn't actually paste, which I think is your real problem. It writesassigns, and has all sorts of potential for problems if the ranges aren't the same size.
OK, so what about a solution?
use strict;
use warnings;
use Win32::OLE;
my $adrData = "A1:B34";
my $adrSource = "C6:C37";
my $adrDest = "D7"; #Notice the offset and the fact that I name on
+ly 1 cell
my $formula = "=SUM(A1:A3)";
my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents} = 0;
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);
my $rngData = $sht->Range($adrData);
for (1..$rngData->Cells->{Count}) { #Set up some random data
#Perl indexes from 0, Excel u
+sually from 1
$rngData->Cells($_)->{Value} = rand; #Don't use Cells in this way
+unless you understand it
}
my $rngSource = $sht->Range($adrSource);
$rngSource->Cells(1,1)->{Formula} = $formula;
#The next line shows you how to copy and paste. PasteSpecial is differ
+ent.
#The paste range includes the copy range, but as it's a single cell, i
+t's
#less work for the machine to overwrite than to calculate the correct
+range.
$rngSource->Cells(1,1)->Copy($rngSource);
for (1..$rngSource->Cells->{Count}) { #This is where we have to do tha
+t pesky regex stuff.
#Without the offset it's not nec
+essary.
$rngSource->Cells($_)->{Formula} =~ s/\(A/\(A\$/;
$rngSource->Cells($_)->{Formula} =~ s/:A/:A\$/;
} #Regex gurus - I'd love to know how to put both on one line
$rngSource->Copy($sht->Range($adrDest)); #Another copy/paste. If the p
+aste range is a single
#cell, Excel will DWIM, amazi
+ngly.
For any Excel types who read this, there's a much more elegant trick using R1C1 notation, but I think that would not be understood.
At this point, I'll stop venting my frustrations on you and try to cope with my real life problems.
Regards,
John Davies |