Reference Excel Chart in Access Unbound Object Frame using Sourceitem property

I can't for the life of me figure out to reference an Excel chart in my Access form Unbound Object Frame using the "Sourceitem" property. I need to embed multiple charts (not just pictures of the chart)into my Access form using the OLE Unbound Object Frame. I have the "SourceDoc" property correct as this is just the filepath to my Excel file. However I can't seem to reference the specific charts on the worksheets (Which are seperate chart sheets from the actual data). Attached is an example of the non-workng code. It currently returns only the active cells in the worksheet. I've been struggling with this for days !!! Thanks.

*The Excel File Name = Spend Management 7-19-2010.xlsx
*The Worksheet(Chart Sheet) Name = Direct-Charts
*Chart Name = Chart 35
With dtb

'.Visible = True
     ' Enable control.
     '.Enabled = True
     ' Set Locked property to False.
     '.Locked = False
     ' Allow embedded objects only.
     '.OLETypeAllowed = acOLEEmbedded
     ' Specify the OLE server and the type of object.
     '.Class = "Excel.Chart.8"
     ' Specify the file to be embedded.
     '.SourceDoc = "G:\000_Supply Chain\Procurement-Corporate\Spend Management\Spend Management 7-19-2010.xlsx"
     ' Specify the Worksheet Item
     '.SourceItem = "Direct - Charts![Spend Management 7-19-2010.xlsx].Chart 35"
     ' Create the embedded object.
     '.Action = acOLECreateEmbed
 ' End With

Open in new window

RichBLACKIIIAsked:
Who is Participating?
 
calacucciaCommented:
In this thread, the syntax is slightly differenet http://www.pcreview.co.uk/forums/thread-1154811.php
Of course, there it's referring to a sheet embedded chart, maybe wort a try to place the Chart in a worksheet and do it this way?

Also here, it is mentioned to be working I believe http://forums.techguy.org/business-applications/568565-open-excel-chart-access-2003-a.html

Have you tried much shorter statements?

'.SourceItem = "Direct - Charts"
(eventually wit [] or '   ' around them?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Have you tried simply copying the Excel chart, then on the Access form, Select:
Paste Special-->Microsoft Office Excel Chart?

This works fine for me...

;-)

JeffCoachman
1
 
RichBLACKIIIAuthor Commented:
No, that's not going to work. I have multiple charts that need to be loaded into the same unbound object frame at different times when I run a different macro through command buttons on my form.

Gosh, I didn't think this question would be this hard ???...I guess it must be if google couldn't provide an answer :\

 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GRayLCommented:
Although it says worksheet, it is actually a workbook.  You have to start from there.  What is the workbook(sheet) name?
0
 
RichBLACKIIIAuthor Commented:
*The Excel File Name = Spend Management 7-19-2010.xlsx
*The Worksheet(Chart Sheet) Name = Direct-Charts
*Chart Name = Chart 35

I have this in the question already.
0
 
Kevin CrossChief Technology OfficerCommented:
I believe your issue to be here:

.SourceItem = "Direct - Charts![Spend Management 7-19-2010.xlsx].Chart 35"

Since the names have spaces in them, I believe you will want to surround them with []. But as I believe was GrayL's point here - http:#a33442720 - you will want to start with the Excel sheet name.

.SourceItem = "[Direct - Charts]![Chart 35]"
or
.SourceItem = "[Direct - Charts].[Chart 35]"

You are already opening the Excel workbook as the source document.
0
 
RichBLACKIIIAuthor Commented:
SourceItem = "[Direct - Charts]![Chart 35]"
or
.SourceItem = "[Direct - Charts].[Chart 35]"

The syntax you gave is still incorrect. The Excel file opens but just embeds the active workbook cells.
0
 
GRayLCommented:
Open the form in design view, display Properties, click Data tab, click SourceItem, press F1 and you get a detailed explanation of how to reference the elements in Excel.
1
 
RichBLACKIIIAuthor Commented:
Yes, GRayL I know how to use the Sourceitem property. The exact problem I am having is how to reference an Excel Chart on a Excel Chart Sheet. The help menu does not give the syntax for that situation.
0
 
Kevin CrossChief Technology OfficerCommented:
VBA > Worksheets("{sheetname}").ChartObjects(n).Chart
http://msdn.microsoft.com/en-us/library/aa213725(office.11).aspx
0
 
RichBLACKIIIAuthor Commented:
VBA > Worksheets("{sheetname}").ChartObjects(n).Chart
http://msdn.microsoft.com/en-us/library/aa213725(office.11).aspx

This does not work with the sourceitem property.
0
 
GRayLCommented:
I got from the Help extract that the SourceItem had to be a cell or cell range.  ie.  It could not be a chart.
0
 
Kevin CrossChief Technology OfficerCommented:
Think GrayL is right here: http:#a33524927
0
 
RichBLACKIIIAuthor Commented:
It's close enough
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.