• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 985
  • Last Modified:

How to reference a union range in Access VBA

From MS Access vba I am trying to generate a chart from excel sheet.  The DataSource for the chart is a non-contiguous range of cells.  When using the Union method on the non-contiguous ranges I receive the Method 'Union' objected _global failed.

Here is the line that is failing:
Set rngCountOfAnsCol = Union(WS.Range("C$12:$C$13"), WS.Range("$E$12:$E$13"), WS.Range("$G$12:$G$13"))

I have tried WB.Union(WS.Range...
Excel.Application.Union(WS.Range.....

I still get errors.
0
kkhan7
Asked:
kkhan7
  • 9
  • 4
  • 3
  • +1
1 Solution
 
Gustav BrockCIOCommented:
You can't do it this - lazy - way. You have to be extremely specific when defining and accessing Excel objects - like this example:

  ' Prefix for Named Ranges (DFI groups).
  Const cstrDFIRangePrefix  As String = "DFIGruppe"
  ' Count of Names Ranges (DFI groups).
  Const cbytDFIRangeCount   As Byte = 30
  ' Column numbers.
  Const cbytDFIArt          As Byte = 1
  Const cbytDFIBudget       As Byte = 8
  Const cbytDFIRealiseret   As Byte = cbytDFIBudget + 1
  Const cbytDFIRest         As Byte = cbytDFIBudget + 2
  Const cbytDFITotal        As Byte = cbytDFIBudget + 3
  Const cbytDFIDifference   As Byte = cbytDFIBudget + 4
 
  Dim wkb                   As Workbook
  Dim wks                   As Worksheet
  Dim wksData               As Worksheet
  Dim rng                   As Range
  Dim rngDFI                As Range
  Dim rngArea               As Range
 
  Dim alngValues(cbytDFIBudget To cbytDFIDifference)  As Long
  Dim lngRows               As Long
  Dim lngColumns            As Long
  Dim bytN                  As Byte
  Dim strDFIRangeSuffix     As String
  Dim booError              As Boolean
  Dim intDFIArtSidste       As Integer

  Set wkb = ActiveWorkbook()
  Set wks = wkb.Worksheets(cstrWorksheetBudgetName)
 
  ' Union Named Ranges to one Named Range.
  With wks
    For bytN = 1 To cbytDFIRangeCount
      strDFIRangeSuffix = Format(bytN, "00")
      If bytN = 1 Then
        Set rngDFI = .Range(cstrDFIRangePrefix & strDFIRangeSuffix)
      Else
        Set rngDFI = Union(rngDFI, .Range(cstrDFIRangePrefix & strDFIRangeSuffix))
      End If
    Next
  End With
 
  Set wksData = wkb.Worksheets(cstrWorksheetDataName)
  Set rng = wksData.Range("A1")

Also be very careful closing all objects in reverse order before you quit Excel.

/gustav
0
 
peter57rCommented:
Gustav
I think the poster is trying to do this from Access via automation code.
0
 
Rory ArchibaldCommented:
Why not just use:

Set rngCountOfAnsCol = WS.Range("C12:C13,E12:E13,G12:G13")

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Gustav BrockCIOCommented:
Yes Peter, missed that, sorry.

Here is how to control Excel from Access VBA given a reference is set to:

    Microsoft Excel 14.0 Object Library

Public Sub RenameWorkSheet()

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    Dim rng     As Excel.Range
    
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)
    
    wks.Name = "My New Name"

    ' Insert range code here.
    Set rng = wks.Range(....)
    ' ---------------------------------

    wkb.Close True
    
    Set rng = Nothing
    Set wks = Nothing
    Set wkb = Nothing
    
    xls.Quit
    
    Set xls = Nothing

End Sub

Open in new window

/gustav
0
 
kkhan7Author Commented:
Hello gustav,

The VBA code you provided doesn't include the Union method.   My problem is that from Access VBA the Union method is not working.  

Do you have any other suggestions?
0
 
Gustav BrockCIOCommented:
Yes, too easy: Excel.Union(...)

/gustav
0
 
Rory ArchibaldCommented:
You don't need union at all here - see my earlier post. ;)
0
 
kkhan7Author Commented:
Hello Rorya,

Yes, on my own I am using currently as a work around to this problem.  I was really hoping to use the Union method.  

Set rngCountOfAnsCol = WS.Range("C12:C13,E12:E13,G12:G13")

The new problem with using the above range the dataseries for the chart has to be set the non-contiguous range as well.  How is that set?  The dataseries is incomplete.  See attached file, tab called Grade_10_chart.

Thanks in advance. ;o)
PivotTableReport-20130107-115142.xlsx
0
 
kkhan7Author Commented:
Gustav,

Excel.Union(Rng1,.....

gives me Method 'Union' object failed _global  err

Do you know why?  Have you tried using it in MS Access 2010 vba to control MS Excel 2010?  Do you have another suggestion?

Thanks in advance :o)
0
 
kkhan7Author Commented:
Rorya,

Attached is the code I am currently using.
CreateChartExcel2010.txt
0
 
Rory ArchibaldCommented:
If you must use Union (which will give you the same outcome as the method I suggested) you need:
WS.Application.Union(WS.Range("C$12:$C$13"), WS.Range("$E$12:$E$13"), WS.Range("$G$12:$G$13"))

Open in new window


Your sample workbook contains pivot charts - you cannot set the range for those; they always use the source pivot table data.
0
 
kkhan7Author Commented:
Rorya,

You are the Man!!!
Yeah  WS.Application.Union  processed perfectly -- NO Error !!!  Thank you sooo much. ;o)

How did you come up with that?  Can you point me to a book or resource?  If it's not too much trouble.

Now the chart is not showing all the items in the legend.  Should I increase chart size?  Or is there some other setting/code I must use?
0
 
kkhan7Author Commented:
Rorya,

Now the chart is not showing all the items in the legend.  Should I increase chart size?  Or is there some other setting/code I must use?

Legend should show grades 6 - 13
PivotTableReport-20130107-122042.xlsx
0
 
Gustav BrockCIOCommented:
You are looking for trouble here:

Set WB = GetObject(sFile)
Set WS = WB.Sheets(shtPivotTable)

Set WB as the declaration as in my demo code.
Also, Sheets are not WorkSheets.

You may get it to work without these steps using strange and messy code, but you may just as well end up with Excel not closing completely.
Did you check the Task Manager that Excel Closes?

/gustav
0
 
kkhan7Author Commented:
Rorya,

What would be the best way to generate a chart for each grade in the resulting pivot table for an non-contiguous data range  or for only the columns called "Count of Answers Received"?

Do I need to create a new sheet for each grade that contains a simple contiguous range of data and then under it the chart?

How do I give more points for this answer?  After you have answered it successfully.
0
 
kkhan7Author Commented:
Thanks Gustav,

I'll check out your suggestions.
0
 
kkhan7Author Commented:
The error Method 'Union' object _global failed didn't occur when I used WS.Application.Union(...  as suggested in the MS Access 2010 vba code to control Excel objects.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now