Export chart as image from excel and load in access - resolution

[diegomarino]

hi, i made this code to export as image a chart and then set and image control in a report to that image, but i'd like to have a better resolution
here the code

Private Sub Report_Load()

    ' Set up variables
    Dim excelApp As Object
    Dim excelWorkbook As Object
    Dim excelChart As Object
    Dim accessApp As Object
    Dim imagePath As String
    
    ' Initialize Excel application
    Set excelApp = CreateObject("Excel.Application")
    excelApp.Visible = False ' You can set this to False if you don't want Excel to be visible
    
    ' Open the Excel workbook
    Set excelWorkbook = excelApp.Workbooks.Open(CurrentProject.Path & "\Grafici.xlsx") ' Update the path accordingly
    
    ' Set a reference to the chart you want to copy (replace "Chart1" with your actual chart name)
    Set excelChart = excelWorkbook.Sheets("CostIncomeGrafico").ChartObjects("Costincome").Chart
    excelChart.ChartArea.Copy
    ' Save the chart as an image file
    imagePath = CurrentProject.Path & "\Immagini Grafici\CostIncome.png" ' Update the path and filename accordingly
    Kill (imagePath)
    excelChart.Export FileName:=imagePath, FilterName:="PNG"
    
    'excelChart.Export FileName:=imagePath, FilterName:="PNG"  ', Width:=800, Height:=600 ' THIS NOT WORKED
    
    ' Set the Picture property of an Image control on the form to the saved image
    'Me.Report("Report-Analisi operazione").Controls("graficoimg").Picture = imagePath ' Replace "ImageControlName" with the actual name of your image control
    Me.GraficoIMG.Picture = imagePath
    '
 Clean up
    Set excelChart = Nothing
    Set excelWorkbook = Nothing


    Set accessApp = Nothing
End Sub

THX

[June7]

What would you think is better?

Is the image always the same path and name?

I have never used code to set Picture property. I use ControlSource property.
Could eliminate that little bit of code by setting ControlSource property with an expression that references the image path: =CurrentProject.Path & "\Immagini Grafici\CostIncome.png"

Or maybe build chart within Access.

[journeyman]

hi, i made this code to export as image a chart and then set and image control in a report to that image, but i'd like to have a better resolution...

So you're effectively copying the chart as a graphic into an access graphic control. Access will store your graphics, but it's not the best solution for historical data tracking

Your better solution might be to:
1. Import that excel data into an access table
2. Build a chart on a form
4. Set the datasource of your chart to the table (or query)
5. Include an import button on your form that opens and imports your excel data anytime you want to append it to your access table.
6. Add a couple of date range textboxes to your form also, and a button to refresh your query criteria and viola. Nifty chart tool for your excel data.

The benefit would be that you could use the access chart to display any historical data you want by modifying the criteria of your query
The cons are that Access charts are notoriously prickly - they are not as easy to work with as excel charts - an you'll have some learning to do ... but thankfully, this is a great forum with loads of knowledgeable people who can help if you ask specific and understandable questions.

[ArviLaanemets]

As it looks like the Excel charts to import into Acccess will be simply pictures, and having a lot of them in Access table will make it's size grow quickly and slow your Access app down as result, I advice another approach:
Have some PDF printer (like CutePDF, which is freeware) installed, and print any charts you want the access from Access to some folder;
In Access, have some form, where you can select a chart (or any other file) from any available folder, and save the link to it into field in currently active Access table record, along with another field which will contains some info helping user to identify the chart;
Create an OnClick event for control displaying chart identify info, which will open the file linked to this table record, using your window's default program for type of linked file (Access itself simply forwards the open the link command to windows).

You can link any types of files, and when you want to display the linked file, it works exactly in same way like when you click on it in File Explorer.

[diegomarino]

I should have been more detailed

I have all my data in access, but i want better charts (i tried even Better Access charts) and the best option is excel.
So i made a workbook called "charts" in which i linked my access query and made a chart based on them.

The next step was to copy the (BEAUTIFUL ) chart from excel to access
I did by picture property but also by control source property, and this is the result. It's not bad, but could be better

[Micron]

Why not open the workbook from Access and look at the Excel chart?
Or use a form with a bound object control?

[diegomarino]

yes, i was thinking about that but i dunno how to do that. I never used the web control (or the new edge)

[June7]

Yes, chart is beautiful but simple. Access should be able to produce it as well.

Who mentioned web control? Suggestion I see was to use bound object control.

[Micron]

If your reply is based on what I posted, then I don't see where a web control or M$ Edge comes into this. I said a bound object control but I should have said "unbound" object frame.
https://learn.microsoft.com/en-us/of...ss.objectframe
Maybe your dissatisfaction with the image approach was caused by the picture file type you used? You could try .bmp or .wmf or .jpg instead of .png

[diegomarino]

sorry, i knew you could link an excel in a web browser, i was barely aware of the objectframe and it is just what i looked for!
thanks very much, now i have excel charts in my access

[Micron]

If I was able to help then 1 more dram of Scotch for me!

[diegomarino]

i have just one question more

this is the code on open

Private Sub Report_Open(Cancel As Integer)   
   DoCmd.SetWarnings False
   DoCmd.RunSQL ("DELETE * FROM CostIncomeGrafico")
   DoCmd.RunSQL ("INSERT INTO CostIncomeGrafico (CostiRicavi,Valore) SELECT CostiRicavi, Valore FROM qryCostincome")
   DoCmd.SetWarnings True
   Call AggiornaExcelGrafico(CurrentProject.Path & "\Grafici\CostIncome.xlsx")
   Me.CostIncomeObj.Requery


End Sub

and this is the function i made to refresh the excel (it has just one connection)

Public Function AggiornaExcelGrafico(WorkbookPath As String)   Dim xlx As Object
   Dim xlw As Object
   
   Set xlx = CreateObject("Excel.application")
   Set xlw = xlx.Workbooks.Open(WorkbookPath)


   
   xlx.DisplayAlerts = False
   xlw.RefreshAll
   xlw.Save
   xlw.Close 'SaveChanges:=True
   xlx.DisplayAlerts = True


   Set xlw = Nothing
   xlx.Quit
   Set xlx = Nothing



End Function

it does not refresh anything, i have to open the excel and click "refreshall" button.

Why that?

[diegomarino]

hi, i took another approach.

this is the button on the form i use to open the form with objectframe

​Private Sub MappaBTN_Click()   Dim xlx As Object, xlw As Object, Xls As Object, xlc As Object
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Worksheetname As String


   DoCmd.SetWarnings False


   DoCmd.RunSQL ("DELETE * FROM OcfXRegione")
   DoCmd.RunSQL ("INSERT INTO OcfXRegione (Regione, Conteggio) SELECT Regione,Conteggio FROM qryOcfXregione")


   Set db = CurrentDb
   On Error Resume Next
   Set xlx = GetObject(, "Excel.application")
   If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.application")
   End If
   Err.Clear
   On Error GoTo 0
   xlx.Visible = False ' se setto vero mi apre il file excel in questione


   Set rst = db.OpenRecordset("OcfXregione")
   If rst.RecordCount > 0 Then




      Set xlw = xlx.Workbooks.Open("C:\Users\diegomarino\OneDrive - Indipendente\Lavoro\Informatica\Gestionale\FrontEnd\Grafici\CFNumeroMappa.xlsx")
      Set Xls = xlw.Worksheets("qryOcfXRegioni")


      Set xlc = Xls.Range("A2")
      Do While Not rst.EOF Or rst.BOF
         xlc = rst.Fields(0).Value
         xlc.Offset(0, 1).Value = rst.Fields(1).Value
         Set xlc = xlc.Offset(1, 0)
         Debug.Print (xlc)
         rst.MoveNext
      Loop
      xlx.DisplayAlerts = False




      'xlw.RefreshAll
'      With xlw.grafico1
'        .SetSourceData Source:=Sheets("qryOCFXRegione").Range("$A$1:$b$21")
'        'other chart formatting code that all works fine
'      End With
      DoEvents
      xlw.Save
      xlw.Close SaveChanges:=True
      xlx.DisplayAlerts = True
   End If


   rst.Close
   Set rst = Nothing
   Set xlc = Nothing
   Set Xls = Nothing
   Set xlw = Nothing
   xlx.Quit ' se esiste l'applicazione excel la chiude
   Set xlx = Nothing




   DoCmd.OpenForm "AnalisiOCFxregione"

   DoCmd.SetWarnings True

then, i have a combobox in which i can filter data (that's very simple data, just 20 rows max with two columns)

Private Sub CercaBancaCB_AfterUpdate()

Dim xlx As Object, xlw As Object, Xls As Object, xlc As Object, oChart As Object
Dim db As DAO.Database, rst As DAO.Recordset
Dim Worksheetname, WorkBookName As String
Dim i As Integer


   
   DoCmd.SetWarnings False
   DoCmd.RunSQL ("DELETE * FROM ocfXregione")
   DoCmd.RunSQL ("INSERT INTO OcfXRegione (REgione, conteggio) " & vbCrLf & _
                 "SELECT Regione, conteggio  " & vbCrLf & _
                 "FROM( " & vbCrLf & _
                 "SELECT Comuni.Regione, Count(IscrittiOCF.regione) AS Conteggio " & vbCrLf & _
                 "FROM Comuni INNER JOIN (OCFComuni INNER JOIN IscrittiOCF ON OCFComuni.COMUNE = IscrittiOCF.COMUNE) ON Comuni.IDComune = OCFComuni.ComuneID " & vbCrLf & _
                 "WHERE (iscrittiocf.regione <> """" AND iscrittiocf.regione <> ""\n"") AND (IscrittiOCF.DEnominazione_soggetto_abilitato = FORMS!Analisiocfxregione!Cercabancacb or FORMS!Analisiocfxregione!Cercabancacb is null) " & vbCrLf & _
                 "GROUP BY Comuni.Regione)")
   Me.Requery
  


   Set db = CurrentDb
   On Error Resume Next
   Set xlx = GetObject(, "Excel.application")
   If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.application")
   End If
   Err.Clear
   On Error GoTo 0
   xlx.Visible = False ' se setto vero mi apre il file excel in questione


   Set rst = db.OpenRecordset("OcfXregione")
   If rst.RecordCount > 0 Then
      rst.MoveLast
      rst.MoveFirst
      WorkBookName = "C:\Users\diegomarino\OneDrive - Indipendente\Lavoro\Informatica\Gestionale\FrontEnd\Grafici\CFNumeroMappa.xlsx"
      
      Set xlw = xlx.Workbooks.Open(WorkBookName)
      xlw.RefreshAll
      Set Xls = xlw.Worksheets("qryOCFXRegioni")
           
      Set xlc = Xls.Range("A2")
      Do While Not rst.EOF Or rst.BOF
         xlc = rst.Fields(0).Value
         xlc.Offset(0, 1).Value = rst.Fields(1).Value
         Set xlc = xlc.Offset(1, 0)
         Debug.Print (xlc)
         rst.MoveNext
      Loop


      Set xlc = Xls.Range("A" & rst.RecordCount + 2)
      i = rst.RecordCount + 2
      Do Until i = 22
         xlc = ""
         xlc.Offset(0, 1).Value = ""
         Set xlc = xlc.Offset(1, 0)
         i = i + 1
      Loop
      
      
      xlx.DisplayAlerts = False


      xlw.Close SaveChanges:=True
      xlx.DisplayAlerts = True
   End If




   rst.Close
   Set rst = Nothing
   Set xlc = Nothing
   Set Xls = Nothing
   Set xlw = Nothing
   xlx.Quit ' se esiste l'applicazione excel la chiude
   Set xlx = Nothing
   DoCmd.SetWarnings True


End Sub

everything work perfectly, except the chart sometimes is not refreshing in objectframe, and when i open the excel it tooks few seconds to show the chart even in excel. Why that?

i'd liek to stick with this method that's the best i found

[diegomarino]

GOT IT! i need just a SLEEP bifore closing the excel application!

[Micron]

Thanks for posting your progress and solution. It may help someone in the future.