Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UNION via Automation = invalid range result!?
Excel Gurus: HELP!
I am automating Excel via VBA from Access. 1) I have valid objects for the Applicaiton, and workbook, and i *know* the ranges I am working with are NOT EMPTY. 2) the ranges ARE all on the same worksheet. the problem: I am building a chart, and the chart has multiple series of data to graph. The chart has a category X axis. The lists of Series Categories aren't necessarily all identical, so I need to build a range which encompases all the unique category values across all the series. My method is to build an initial list of known-unique names (a subset of the final list) and use a little code in nested for each loops to identify all those items in subsequent ranges which aren't already in the initial range, and UNION the cell.Address(External:=True) to the previous range (making a discontiguous range that points to a complete but also unique list of category values. The problem I am having is that when I call the UNION method as follows, the result is an INVALID range address string! Ex: Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Debug.Print "n.address="; n.Address(External:=True) oTempDRI.RangeAddress = _ xlA.Union( _ xlA.Range(oTempDRI.RangeAddress), _ xlA.Range(n.Address(External:=True)) _ ).Address(External:=True) Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Result in debug window: Pre-UNION oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26 Post-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 Pre-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28 The 2nd pass through my loop, the .Range method blows up on the UNION-supplied address string (even with (External:=True) being used?! Any thoughts/advice? ....or am I just going to have to do some string surgery to manually stuff the "[...]...!" part of the address back into the resultant string after the last "," myself? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UNION via Automation = invalid range result!?
Just reading the subject line I anticipated the problem was going to be not
qualifying Excel's Union function with a reference to the Excel Application, but I see you have done that. I can't make out from your code if the arguments in the Union function refer to valid ranges on the same sheet or if they are returned addresses. I can't see for the clutter but I suspect not, ie they should be range objects and not addresses. Regards, Peter T "Mark Burns" wrote in message ... Excel Gurus: HELP! I am automating Excel via VBA from Access. 1) I have valid objects for the Applicaiton, and workbook, and i *know* the ranges I am working with are NOT EMPTY. 2) the ranges ARE all on the same worksheet. the problem: I am building a chart, and the chart has multiple series of data to graph. The chart has a category X axis. The lists of Series Categories aren't necessarily all identical, so I need to build a range which encompases all the unique category values across all the series. My method is to build an initial list of known-unique names (a subset of the final list) and use a little code in nested for each loops to identify all those items in subsequent ranges which aren't already in the initial range, and UNION the cell.Address(External:=True) to the previous range (making a discontiguous range that points to a complete but also unique list of category values. The problem I am having is that when I call the UNION method as follows, the result is an INVALID range address string! Ex: Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Debug.Print "n.address="; n.Address(External:=True) oTempDRI.RangeAddress = _ xlA.Union( _ xlA.Range(oTempDRI.RangeAddress), _ xlA.Range(n.Address(External:=True)) _ ).Address(External:=True) Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Result in debug window: Pre-UNION oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26 Post-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 Pre-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28 The 2nd pass through my loop, the .Range method blows up on the UNION-supplied address string (even with (External:=True) being used?! Any thoughts/advice? ...or am I just going to have to do some string surgery to manually stuff the "[...]...!" part of the address back into the resultant string after the last "," myself? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UNION via Automation = invalid range result!?
Peter,
If they were only addresses, the UNION method would error out. It "succeeds". I have since discovered that even of you do "fixup" the external references yourself, if you subsequently pass even those fixed-up external addresses into UNION again later, *all* the external addressing semantics are lost except for the FIRST one! However, this is all moot, as I also subsequently discovered that the charting component apparently must have CONTIGUOUS ranges to use for its axes/categories/labels range arguments. "Peter T" wrote: Just reading the subject line I anticipated the problem was going to be not qualifying Excel's Union function with a reference to the Excel Application, but I see you have done that. I can't make out from your code if the arguments in the Union function refer to valid ranges on the same sheet or if they are returned addresses. I can't see for the clutter but I suspect not, ie they should be range objects and not addresses. Regards, Peter T "Mark Burns" wrote in message ... Excel Gurus: HELP! I am automating Excel via VBA from Access. 1) I have valid objects for the Applicaiton, and workbook, and i *know* the ranges I am working with are NOT EMPTY. 2) the ranges ARE all on the same worksheet. the problem: I am building a chart, and the chart has multiple series of data to graph. The chart has a category X axis. The lists of Series Categories aren't necessarily all identical, so I need to build a range which encompases all the unique category values across all the series. My method is to build an initial list of known-unique names (a subset of the final list) and use a little code in nested for each loops to identify all those items in subsequent ranges which aren't already in the initial range, and UNION the cell.Address(External:=True) to the previous range (making a discontiguous range that points to a complete but also unique list of category values. The problem I am having is that when I call the UNION method as follows, the result is an INVALID range address string! Ex: Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Debug.Print "n.address="; n.Address(External:=True) oTempDRI.RangeAddress = _ xlA.Union( _ xlA.Range(oTempDRI.RangeAddress), _ xlA.Range(n.Address(External:=True)) _ ).Address(External:=True) Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Result in debug window: Pre-UNION oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26 Post-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 Pre-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28 The 2nd pass through my loop, the .Range method blows up on the UNION-supplied address string (even with (External:=True) being used?! Any thoughts/advice? ...or am I just going to have to do some string surgery to manually stuff the "[...]...!" part of the address back into the resultant string after the last "," myself? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UNION via Automation = invalid range result!?
Afraid I didn't concentrate while reading your post.
FWIW, generally charts do not require ranges to be contigous or even same size. eg following for a single series with values in discontiguous ranges - s = "=(" & rng.Areas(1).Address(, , xlR1C1, True) For i = 2 To rng.Areas.Count s = s & "," & rng.Areas(i).Address(, , xlR1C1, True) Next s = s & ")" sr.Values = s ' similarly sr.XValues where rng is a multi-area range and sr is a Series object Of course not necessary to Union & create a multi area range, other ways to build a similar string. Regards, Peter T "Mark Burns" wrote in message ... Peter, If they were only addresses, the UNION method would error out. It "succeeds". I have since discovered that even of you do "fixup" the external references yourself, if you subsequently pass even those fixed-up external addresses into UNION again later, *all* the external addressing semantics are lost except for the FIRST one! However, this is all moot, as I also subsequently discovered that the charting component apparently must have CONTIGUOUS ranges to use for its axes/categories/labels range arguments. "Peter T" wrote: Just reading the subject line I anticipated the problem was going to be not qualifying Excel's Union function with a reference to the Excel Application, but I see you have done that. I can't make out from your code if the arguments in the Union function refer to valid ranges on the same sheet or if they are returned addresses. I can't see for the clutter but I suspect not, ie they should be range objects and not addresses. Regards, Peter T "Mark Burns" wrote in message ... Excel Gurus: HELP! I am automating Excel via VBA from Access. 1) I have valid objects for the Applicaiton, and workbook, and i *know* the ranges I am working with are NOT EMPTY. 2) the ranges ARE all on the same worksheet. the problem: I am building a chart, and the chart has multiple series of data to graph. The chart has a category X axis. The lists of Series Categories aren't necessarily all identical, so I need to build a range which encompases all the unique category values across all the series. My method is to build an initial list of known-unique names (a subset of the final list) and use a little code in nested for each loops to identify all those items in subsequent ranges which aren't already in the initial range, and UNION the cell.Address(External:=True) to the previous range (making a discontiguous range that points to a complete but also unique list of category values. The problem I am having is that when I call the UNION method as follows, the result is an INVALID range address string! Ex: Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Debug.Print "n.address="; n.Address(External:=True) oTempDRI.RangeAddress = _ xlA.Union( _ xlA.Range(oTempDRI.RangeAddress), _ xlA.Range(n.Address(External:=True)) _ ).Address(External:=True) Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress Result in debug window: Pre-UNION oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26 Post-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 Pre-UNION oTempDRI.RangeAddress= [Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26 n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28 The 2nd pass through my loop, the .Range method blows up on the UNION-supplied address string (even with (External:=True) being used?! Any thoughts/advice? ...or am I just going to have to do some string surgery to manually stuff the "[...]...!" part of the address back into the resultant string after the last "," myself? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union range | Excel Discussion (Misc queries) | |||
union range problem | Excel Programming | |||
Excel Not showing any result after Automation | Excel Programming | |||
Union/Range/Cells | Excel Programming | |||
Union method for Range Object | Excel Programming |