SourceItem (1 Viewer)

Mal Ba

New member
Local time
Today, 13:31
Joined
Mar 6, 2021
Messages
22
For an unbound object frame what is the format of the .SourceItem property?
I have a chart in an Excel file which I am trying to reference as the source.
The chart is a Sheet called [Graphs] and the chart named is [Chart 1]
SourceDoc = C:\Users\Mal\Downloads\MapTransfer.xlsx
and I can't see what could be wrong with that, but I cannot reference the chart in that file:
I've tried:
Chart 1
[Chart 1]
[Graphs]![Chart 1]
[Graphs].[Chart 1]
Graphs!Chart 1
Graphs.Chart 1
MapTransfer.xlsx![Graphs]![Chart 1]
[MapTransfer.xlsx]![Graphs]![Chart 1]
and countless others but none seem to display the chart.

Other properties, which I think are right, but I give just in case they are aren't:
OLEType = Embedded
OLETypeAllowed = Either
OLEClass = Microsoft Excel 2003
Class = Excel.Chart.8

Both files are the most recent Office 365 releases.

I'm guessing the .SourceItem is not specified correctly. Anyone know how I should refer to the chart?

Mal
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
14,156
just Export the Chart as Image and use the Image
as Picture propety of an Image Control on the form.
 

Mal Ba

New member
Local time
Today, 13:31
Joined
Mar 6, 2021
Messages
22
That's what I had actually done as a temporary measure. I rather hoped it would be possible to do it using the ImageControl which would then have refreshed automatically as it does for tables rather than having to code it up.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
14,156
use a Bound Object Frame, instead of Unbound object frame.
on load event of your form:

Code:
Private Sub Form_Load()
With Forms.Item("Form1").Controls.Item("OLEBoundControlNameHere")
    .Class = "Excel.Sheet" ' Set class name.
    .OLETypeAllowed = acOLELinked
    .SourceDoc = "C:\Users\Mal\Downloads\MapTransfer.xlsx"
    .SourceItem = "Chart 1"
    .Action = acOLECreateLink
    .SizeMode = acOLESizeStretch
End With
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom