Issues with updating Excel chart based on Access Data and subsequent embedded chart on Access form

[edmscan]

Ok .. first I have never done this before. But .. I really like the potential so I am doing this in Access and Excel.



But .. I am having issues with the data being refreshed. The reason I use Excel is that I cannot get Access to do at all close to the things I can do in Excel with regards to the charts.

Original data is from an Access query. (Access 2010)
I have created Excel chart based on above access data. That part is fine.
I then have an unbound object frame with the Excel chart (Excel 2010) above on an Access form. (the Access data and Access form is in the same database).

So .. how do I make it so that the Chart in the Access form is updated whenever the data in Access is updated ?

I imagine I need to update the data in Excel first and then update the Excel chart on the Access form update based on the changed Excel chart.

I am fine with using a command button to force a refresh of the data ... and subsequent chart.

Kevin

[June7]

Is the Excel chart a pivot?

I have never tried to embed Excel chart.

If you want to provide your files for analysis, follow instructions at bottom of my post.

[Micron]

From what I've read, it's supposed to update when the underlying data changes, presuming when you create the control you link to the source rather than embed it. So that's a confession that I've never used it (preferred to send data to Excel via automation and open the spreadsheet file). You could use the double click event on the control instead of a button if desired (for a list of properties and methods on the unbound frame see https://msdn.microsoft.com/en-us/lib.../ff195122.aspx.

To manually update if the form is already open and current but the data has changed, I would try the form repaint method or here's a post with a similar issue. Seems the solution was to refresh the Excel file.
https://social.msdn.microsoft.com/Fo...orum=accessdev

[edmscan]

Thank you for your replies. I almost got there .. but the problem I found is that I could not update the data in Excel while the Access database was open. It complained that I had it open. (the database was of course the source of the underlying data).

I did try using another database .. and go from there but it was a bit messy, but I think that is open to me if need be.

So .. I am thinking I will try it directly from Access. I only want a simple line chart mostly .. and I think I can do it directly without Excel in the picture.

I did read that Access developers were complaining that Access does not have the vast ability for charts etc that Excel does. You would think that this would be a welcome edition.

I will post back in a few days probably once I have had some time to see what I can do.

June .. I cannot really post the files as it is financial data that is confidential. But it is only an unbound frame (embedded from Excel). It does work if you open Excel and press the refresh button .. and then do a refresh of the unbound frame in Access.

But I want something a lot more automated where I can directly just open the form and the frame is updated.

The code posted above does seem promising, and is very much like I was doing. I think it may just need some tweaking.

[June7]

Yes, Access graphing not as versatile as Excel but I have been able to meet my graphing needs with Access chart object and VBA to manipulate the charts.

The data can be 'dumbed down' for testing of design.

[edmscan]

Yes, Access graphing not as versatile as Excel but I have been able to meet my graphing needs with Access chart object and VBA to manipulate the charts.

The data can be 'dumbed down' for testing of design.

I have abandoned doing it in Access directly. My Excel chart is just beautiful. And ... done in like 15 min.

Now just need to perfect the refresh part. I will post back my success or failure .. likely tomorrow.

I am very very sure that I will find the part I need as I am not really reinventing the wheel here.

[edmscan]

I have abandoned doing it in Access directly. My Excel chart is just beautiful. And ... done in like 15 min.

Now just need to perfect the refresh part. I will post back my success or failure .. likely tomorrow.

I am very very sure that I will find the part I need as I am not really reinventing the wheel here.

I think I have the refresh of the data in Excel working ... without any issues.

Now I just need to update the Unbound OLE frame in the Access form .. that seems to be a bit harder.

[edmscan]

Ok .. determined that the below code does indeed update the data in Excel from Access.

And if I put it in the open event of the form .. the data refreshes on the unbound frame back in the form in Access.

This is a done deal. ;-)

' Begin code ..

Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")

With oExcel.Application
.Visible = False
.Workbooks.Open "C:\Users\Kevin.Win8\Desktop\Finance\Finance.x lsx"
.ActiveWorkbook.RefreshAll
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.Quit
End With

Set oExcel = Nothing

' End Code ..

Thanks to all that helped on this one.

- Kevin -

[June7]

Does Me.Refresh accomplish anything?

[edmscan]

It was a weird one .. I tried that and anything else I could think of.

But then .. I just thought why do I not put the code on the open event and it works perfectly. I have Locked = No and Enabled = No so that I cannot double click the frame and get into Excel as there is no need to go to Excel at all.
If I set locked to yes .. then it no longer works. So .. this is one for the books.

[Micron]

If I set locked to yes .. then it no longer works. So .. this is one for the books.

It's already documented. Have seen it on some forum, but probably couldn't find it now. It is in the setup code here though... https://support.microsoft.com/en-us/kb/209990

[edmscan]

Does Me.Refresh accomplish anything?

I did try Me.Refresh on a command button and it did not refresh the data on the unbound frame.