So - at my job here - they have this "report" which is actually an Excel
spreadsheet output.
There was a recent request to re-org part of that excel file. The generated
file is actually based upon a Template - then data gets inserted via Office
Automation.
My issue is in regards to swapping around these 2 sections of info on the excel
file. There are 2 sets of labels & data, currently one is above the other. They
want them reversed. Problem is - the bottom section can grow. So - in now
making it appear directly above the other section - that 2nd section must be
pushed down in the spreadsheet, based upon the # of Rows being added.
I initially tried moving the whole Range down. But, Excel gave an error - since
the 1st row of that set will end up appearing in the middle of where that set
used to be. So - at this point - I changed the code to move the lines down - a
single line at a time - as such:
SELECT (ytdCalcs)
ncell = RECCOUNT()+16
ShiftCount = RECCOUNT()
WITH oExcel
FOR XclRow = 4 TO 0 STEP -1
CurrRowNo = 17 + XclRow
cRange_From = "A" + ALLTRIM(STR(CurrRowNo)) + ":B" +
ALLTRIM(STR(CurrRowNo))
.ActiveSheet.RANGE(cRange_From).SELECT
.SELECTION.Cut
cRange_To = "A" + ALLTRIM(STR(CurrRowNo+ShiftCount)) ;
+ ":B" + ALLTRIM(STR(CurrRowNo+ShiftCount))
.RANGE(cRange_To).SELECT
.SELECTION.INSERT(xlDown)
ENDFOR
ENDWITH
As you can probably guess - ShiftCount is the amount of Rows by which I want to
shift the data set downwards. And - xlDown variable is defined farther above in
the program as such:
* XlDirection
#DEFINE xlDown -4121
Problem is - below this set there is a Note type text - that spans 5 Columns x
2 Rows - and is Text Wrapped & Merged. When one of my Insert commands above
Runs (1st time in the loop - and it's the last row of the set of cells) - then
Excel gives a warning - "This operation will cause some merged cells to
unmerge. Do you wish to continue?" However, as I stepped thru the code - and it
Ran the Insert - the Range of cells into which the cells were to be inserted -
were NOT Merged! In fact - that Merged Text note - it was about 8 Rows farther
down. And, yet - after the Insert operation - that Text note was now Unmerged!
And, I really need this Text note to be Merged!
To be more specific - Range A21:B21 is trying to be moved to A24:B24 - while
the Text note was in A32:E33
Strangely enough - the cRange_To had the value of "A24:B24" - and yet, after
the INSERT command ran - those 2 cells now resided in A23:B23! WTF???
FYI - I did try searching the ProFox Archives - but, could not find a specific
reference to the Insert command having to do with Automation. I found a
reference to the VFUG website - and Matt Jarvis doing a whole series on Office
Automation - but, I could not find that series after creating an account on
VFUG!
TIA,
Kurt Wendt
Consultant
GLOBETAX
90 Broad Street
New York, NY 10004-2205, U.S.A.
Tel. +1-212-747-9100
Fax. +1-212-747-0029
Email: [email protected]
Web: www.GlobeTax.com
GlobeTax does not provide or offer, and this is not, tax, investment or legal
advice. This email and any attachments hereto is intended only for use by the
addressee(s) and may contain confidential information. If you are not the
intended recipient of this e-mail, please immediately notify the sender at
+1-212-747-9100 and permanently delete the original and any copies of this
e-mail.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/80838f1ca795b14ea1af48659f35166f265...@drexch02.corp.globetax.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
