1

I have an access form where I have included an excel chart as an unbound ole object. (MS Graphs in access are so terrible!) The excel sheet is an independent file, outside the database. Through a DoCmd.TransferSpreadsheet instruction I can export the results of a query to the excel sheet. The graph in excel updates itself, but no change appears in the form, and I don't know how to force the update. I have tried to update the object with the .requery statement, but it does not update. If I manually open the object from the form it does.

Any way to force the update immediately after doing the export?

Thank you very much

salvaleuven
  • 21
  • 4
  • All of my charting in Access uses MSGraph and the results are adequate for my needs. What did you find lacking? Does this help https://learn.microsoft.com/en-us/office/vba/api/Access.ObjectFrame.UpdateOptions – June7 Jun 16 '22 at 04:38
  • possible duplicate of: https://stackoverflow.com/questions/14895659/embed-a-generated-excel-chart-in-access – mazoula Jun 16 '22 at 08:09
  • @june7 thanks, i've already checked that property is set to automatic. With MSGraph It's a pain when there's no data to display in the chart, as it losses the appearance of plots, series, points, axes, etc. Also i get erratic error messages as "there was a problem as MS Access communicates with OLE server"..i've 4 graphs to display in the same form, each with its own set of data. – salvaleuven Jun 16 '22 at 08:18
  • check that the unbound frame is linked to the chart in excel. (I had to also move the chart to its own spreadsheet and could only get this to work if only had one chart.). So re-create the unbound frame, link it to the chart in the file and select the link checkbox and see if that works – mazoula Jun 16 '22 at 09:22
  • @mazoula i've recreated the unbound frame, linked it to the chart in the file, clicked the link checkbox and...the same: the chart gets refreshed only by clicking the object; then it opens and refreshes correctly. – salvaleuven Jun 16 '22 at 10:38

1 Answers1

1

Well, it seems i've found some kind of solution to this. I've created a bounded frame to the excel chart. Once the base data of the chart is updated, just setting the focus in the frame through VBA updates the chart. It's the same as one-clicking the frame. This works only for bounded frames. It doesn't work for unbounded frame, in spite of linking it to the excel file.

I don't understand why, but it works for me.

Thanks everyone.

salvaleuven
  • 21
  • 4
  • It looks like I was unable to reproduce the problem. the unbound object frame worked in access 2016 – mazoula Jun 16 '22 at 11:22
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 18 '22 at 05:11