Solved

How to reference a union range in Access VBA

Posted on 2013-06-30
17
855 Views
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...
Excel.Application.Union(WS.Range.....

I still get errors.
0
Comment
Question by:kkhan7
  • 9
  • 4
  • 3
  • +1
17 Comments
 
LVL 49

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)
      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
 
LVL 77

Expert Comment

by:peter57r
ID: 39289295
Gustav
I think the poster is trying to do this from Access via automation code.
0
 
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

0
 
LVL 49

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
    
    xls.Quit
    
    Set xls = Nothing

End Sub

Open in new window

/gustav
0
 

Author Comment

by:kkhan7
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?
0
 
LVL 49

Expert Comment

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

/gustav
0
 
LVL 85

Expert Comment

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

Author Comment

by:kkhan7
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)
PivotTableReport-20130107-115142.xlsx
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:kkhan7
ID: 39290506
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
 

Author Comment

by:kkhan7
ID: 39290550
Rorya,

Attached is the code I am currently using.
CreateChartExcel2010.txt
0
 
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.
0
 

Accepted Solution

by:
kkhan7 earned 0 total points
ID: 39290614
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
 

Author Comment

by:kkhan7
ID: 39290622
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
 
LVL 49

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?

/gustav
0
 

Author Comment

by:kkhan7
ID: 39290762
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
 

Author Comment

by:kkhan7
ID: 39290929
Thanks Gustav,

I'll check out your suggestions.
0
 

Author Closing Comment

by:kkhan7
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now