How to reference a union range in Access VBA

Posted on 2013-06-30
Last Modified: 2013-07-06
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...

I still get errors.
Question by:kkhan7
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +1
LVL 50

Expert Comment

by:Gustav Brock
ID: 39289271
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)
        Set rngDFI = Union(rngDFI, .Range(cstrDFIRangePrefix & strDFIRangeSuffix))
      End If
  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.

LVL 77

Expert Comment

ID: 39289295
I think the poster is trying to do this from Access via automation code.
LVL 85

Expert Comment

by:Rory Archibald
ID: 39289917
Why not just use:

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

Open in new window

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 50

Expert Comment

by:Gustav Brock
ID: 39290050
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
    Set xls = Nothing

End Sub

Open in new window


Author Comment

ID: 39290292
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?
LVL 50

Expert Comment

by:Gustav Brock
ID: 39290319
Yes, too easy: Excel.Union(...)

LVL 85

Expert Comment

by:Rory Archibald
ID: 39290330
You don't need union at all here - see my earlier post. ;)

Author Comment

ID: 39290496
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)

Author Comment

ID: 39290506


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)

Author Comment

ID: 39290550

Attached is the code I am currently using.
LVL 85

Expert Comment

by:Rory Archibald
ID: 39290556
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.

Accepted Solution

kkhan7 earned 0 total points
ID: 39290614

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?

Author Comment

ID: 39290622

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
LVL 50

Expert Comment

by:Gustav Brock
ID: 39290647
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?


Author Comment

ID: 39290762

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.

Author Comment

ID: 39290929
Thanks Gustav,

I'll check out your suggestions.

Author Closing Comment

ID: 39303700
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question