Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-12-2014, 06:58 AM   #1
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Create Excel chart from Access VBA (AC2007)

Hi guys,

I'm trying to put together a function to create a pareto chart in Excel from a module in Access VBA.

Code:
Dim serValues As Object
Dim chtPareto As Object
 
Set chtPareto = appExcel.Charts.Add
 
With chtPareto
.Name = "Pareto Chart" .Move After:=objWorkbook.Sheets(objWorkbook.Sheets.Count) .ChartType = 51 ' xlColumnClustered Set serValues = .SeriesCollection.NewSeries With serValues
.Name = piv.DataLabelRange.Text .Values = objPivotSheet.Range(rngValues.Address) .XValues = objPivotSheet.Range(rngLabels.Address)
End With
End With
appExcel, objWorkbook, piv, rngValues & rngLabels are late-bound objects referring to the Excel Application, target workbook, pivot table and appropriate ranges respectively (these are all working fine)

The problem I'm having is, when the code hits the line in blue (to create the new chart object), I don't get a blank chart? Weirdly, the chart is already populated with values from my pivot sheet (even though this is supposed to be a blank chart, the series of which I am about to define programmatically?)

So when it gets to the line in red, I get a 1004 error :

Quote:
Application-defined or object-defined error
How do I get VBA to create a new - blank - chart which I can then assign series to?? Is it possible to create Excel charts from Access using late-binding or does one have to include a reference to the Excel object library?

Thanks

AOB

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 05-12-2014, 08:30 AM   #2
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Re: Create Excel chart from Access VBA (AC2007)

I ran across an issue once where if you do not kill an object it hangs around and caused wacky things to happen, I wonder if your issue could be the same. Try rebooting the pc to delete all objects and run it - ?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-12-2014, 09:19 AM   #3
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Create Excel chart from Access VBA (AC2007)

Thanks Ken

I think the problem is actually that the data driving the charts is in a pivot table. When a cell within the pivot is selected, Excel - in its infinite wisdom - predetermines that a pivot chart is what is required and you can't edit the data source of a pivot chart.

So what I've done is, once the pivots are created, I copy/paste the table range over itself (values only) so i'm just left with the raw data from the table (and none of the associated functionality) Working now.

I wish Access had a decent charting tool so I wouldn't have to resort to passing stuff over to Excel and back...

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 05-12-2014, 10:25 AM   #4
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Re: Create Excel chart from Access VBA (AC2007)

Actually - I am pretty sure it's the same 'Chart Engine', manipulating it just seems harder in Access...

FYI - I hate pivots because the cols can be dyanamic. Maybe if you recreate without the pivot - ?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-13-2014, 12:13 AM   #5
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Create Excel chart from Access VBA (AC2007)

It definitely doesn't have the breadth of functionality as Excel does. Which is frustrating as I believe you're right, it is fundamentally the same engine.

I also hate pivots but they are useful for grouping and summarising data (in Excel), hence I create the pivot to get my summary and then copy / paste(special) to remove the cache and leave me with unformatted / unpivotted data.

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Use Excel.WorksheetFunction.Vlookup from Access VBA (AC2007) AOB Modules & VBA 1 05-06-2014 03:03 AM
Can I create many of chart in one pivot chart in access? Elhbeb Forms 14 01-14-2013 03:36 PM
Create Excel pivot chart from within Access projecttoday General 0 08-13-2011 11:11 AM
Can I create this Excel Chart in Access? ions General 1 08-10-2007 04:46 AM
use array in access module to create excel chart pungentSapling Modules & VBA 1 02-10-2003 11:00 AM




All times are GMT -8. The time now is 06:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World