SourceItem (1 Viewer)

Mal Ba

New member
Local time
Today, 20:36
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
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
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, 20:36
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
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
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:

salvaleuven

New member
Local time
Today, 21:36
Joined
Jun 17, 2022
Messages
5
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
Thank you! I've used this approach and had success with a scatter excel graph in an access form. But i have two graphs in the same form, and it shows the following error: 2115 run time error (sorry is in spanish). It says something related to the validation rule or beforeupdate properties of this field (don't know what field is talking about) that prevents access froms saving the field data.The data of each graph is in its own independent excel file.

When working with only one graph, everything is perfect.

1655470565824.png


I've created a sub instruction for each graph, so i can call them from other events of my form. The graphs are hidden until the user press a button, so first they get the property visible=yes and after that, the following code is executed:

Code:
Public Sub ExpDatoGrafRecPromCCR1()
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12, TableName:="QryrecPromCCR1", FileName:="D:\BASES DE DATOS\CC FQ\CCFQGrafCCR1.xlsx", Range:="QryrecPromCCR1"
'This exports data from access to excel

'Now i assign the excel data to the bound object frame:

With Forms.Item("Ensayos").Controls.Item("GrafRecPromCCR1")
    .Class = "Excel.Sheet" ' Set class name.
    .OLETypeAllowed = acOLELinked
    .SourceDoc = "D:\BASES DE DATOS\CC FQ\CCFQGrafCCR1.xlsx"
    .SourceItem = "GrafRecPromCCR1"
    .Action = acOLECreateLink
    .SizeMode = acOLESizeStretch
End With
End Sub
Public Sub ExpDatoGrafRecPromMR1()
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12, TableName:="QryrecPromMR1", FileName:="D:\BASES DE DATOS\CC FQ\CCFQGrafMR1.xlsx", Range:="QryrecPromMR1"
With Forms.Item("Ensayos").Controls.Item("GrafRecPromMR1")
    .Class = "Excel.Sheet" ' Set class name.
    .OLETypeAllowed = acOLELinked
    .SourceDoc = "D:\BASES DE DATOS\CC FQ\CCFQGrafMR1.xlsx"
    .SourceItem = "GrafRecPromMR1"
    .Action = acOLECreateLink
    .SizeMode = acOLESizeStretch
End With
End Sub

Thank you very much for helping
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
does the Error message has "Debug" option in it.
try pressing the button and write which line and code does it errors.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
you can experiment embedding the chart instead:
Code:
With Me!OLEBound1
    .Class = "Excel.Sheet" ' Set class name.
    .OLETypeAllowed = acOLEEmbedded 'acOLELinked
    .SourceDoc = Environ$("UserProfile") & "\documents\myChart1.xlsx"
    .SourceItem = "Chart1"
    .Action = acOLECreateEmbed   ' acOLECreateLink
    .SizeMode = acOLESizeStretch
End With
 

salvaleuven

New member
Local time
Today, 21:36
Joined
Jun 17, 2022
Messages
5
Thanks, but it gives the same error: 2115 runtime error....

It's pretty strange it works with one chart, but not with two...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
maybe your chart is too complex.
i dont have problem on simple one.
2022-06-17 23 58 08.png
 

salvaleuven

New member
Local time
Today, 21:36
Joined
Jun 17, 2022
Messages
5
Weel, it could be. But the complexity is hadled by excel, not by access. Here you can see the app with the error. When you clic on "Exactitud", the code is executed.

1655482077576.png
 

GPGeorge

Grover Park George
Local time
Today, 13:36
Joined
Nov 25, 2004
Messages
1,776
So there is a validation rule of some type, or perhaps a datatype mismatch, preventing the query from saving incompatible data into a field in the table on which the validation rule is defined. What data is it? Why doesn't it pass validation? Or what data is being saved into a field with an incompatible data type?
 

salvaleuven

New member
Local time
Today, 21:36
Joined
Jun 17, 2022
Messages
5
Well that's what i'd like to know!!! The charts are shown when you click that button. There's no data saving at all, only linking the OLE object to the excel chart. In the code above it can be read what happens. The onclick() event of the button calls the two subs described in my first post.

If i delete one chart (doesn't matter wich one) then there's no error.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:36
Joined
Feb 19, 2002
Messages
42,975
Have you tried to build the charts in Access? The old charts are pretty flexible. the modern charts are simplified for ease of use but don't have the range of options you may need
 

Users who are viewing this thread

Top Bottom