Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reference Excel Chart in Access Unbound Object Frame using Sourceitem property

Posted on 2010-08-12
18
Medium Priority
?
2,894 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:RichBLACKIII
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33426178
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
 

Author Comment

by:RichBLACKIII
ID: 33426617
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
 
LVL 44

Expert Comment

by:GRayL
ID: 33442720
Although it says worksheet, it is actually a workbook.  You have to start from there.  What is the workbook(sheet) name?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:RichBLACKIII
ID: 33442755
*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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 33498411
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
 

Author Comment

by:RichBLACKIII
ID: 33504675
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
 
LVL 44

Expert Comment

by:GRayL
ID: 33504842
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.
0
 

Author Comment

by:RichBLACKIII
ID: 33505376
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 33505512
VBA > Worksheets("{sheetname}").ChartObjects(n).Chart
http://msdn.microsoft.com/en-us/library/aa213725(office.11).aspx
0
 

Author Comment

by:RichBLACKIII
ID: 33515482
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
 
LVL 44

Expert Comment

by:GRayL
ID: 33524927
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
 
LVL 17

Accepted Solution

by:
calacuccia earned 1500 total points
ID: 33537682
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 33703772
Think GrayL is right here: http:#a33524927
0
 

Author Closing Comment

by:RichBLACKIII
ID: 33727532
It's close enough
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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