How to connect Access, Excel & Powerpoint

[doma23]

Currently I'm working on development of an access application that deals with some fiancial data.

I need a button on access form that will when clicked open powerpoint file and create and insert all the graphs from the underlying access data.

This is what I've done so far:
I've created a macro that extract necessary access data from a predefined query and converts it to xls. Then I've made the second xls file that extracts the data from the first xls file, and I've added graph to that second xls based on those extracted data. If I've added the graph to the first xls file it would be overwrited and erased each time the button is clicked.
Furthermore, I've created a ppt file that extract graph from the second xls file.
After creating all that, I've added VBA code to be evoked on the click of the button on my form. VBA code first run macro, and therefore creates first xls file, and after that it calls sub which opens ppt file that I've created previously.

Here are the problems. After macro creates the first xls file, the second xls file is not updated, i guess both xls files need to be opened at the same time for the second xls file to show updated datas. Obviously ppt file is also showing unupdated informations.
The second thing is that I also don't want Powerpoint to ask user if he wants to update all the graphs, but to do it automatically for him. This is not an issue now, but may occur on some instances.

And since there will be lot of graphs, there is a third thing that I don't know how to do, but would like to implement. I want all the necessary access data to be extracted to one xls file, on different worksheets, so each graph would have it's own worksheet in the same xls file.

I somehow feel that there should be much simpler solution, but I can't find it yet.
Hope I was clear and that somebody will be able to help.

Thank you in advance!

[NeoPa]

Are you using Application Automation for this Doma?

Are you using DoCmd.TransferS preadsheet() to pass the data to Excel in the first place?

[doma23]

From this what I've read regarding Application Automation I believe I am. This is the sub which I'm calling to open ppt file:

Sub PPT_OPEN()
 
' Create powerpoint page
    Dim pptPres As PowerPoint.Presentation
    Dim pptApp As PowerPoint.Application
    Dim pptSlide As PowerPoint.Slide
    Dim file As String
    file = "C:\access\test_v1.ppt"
 
    Set pptApp = CreateObject("PowerPoint.Application")
    pptApp.Visible = True
    Set pptPres = pptApp.Presentations.Open(file)
 
End Sub

As for the code you've mentioned DoCmd.TransferS preadsheet() , I'm not using it.
This is the code behind the button click:

Private Sub cmdRunPpt_Click()
DoCmd.RunMacro "EXPORT_ROAC"
Call PPT_OPEN
End Sub

And this is basically the code of the macro (transformed to VBA):

DoCmd.OutputTo acQuery, "ROAC_Q", "MicrosoftExcelBiff8(*.xls)", "C:\ACCESS\DATA\ROAC.XLS", False, "", 0

[doma23]

Ok. Here comes the update.
I completely removed the second xls file as it's not really needed as I now figured how to extract data to a particular worksheet. By doing this I kind of a solved the update problem in ppt (when there are multiple xls fles connected).
I deleted the macro that was used for creation of xls file and have put the vba code instead.
The vba code is using DoCmd.TransferS preadsheet and it's placing data in a particular worksheet of xls file.

After that I've created needed graphs in xls file and just copied them to the ppt file.
They are linked to xls through datasheet in ppt. I'm not sure how good this solution is, as it will probably increase the size of ppt file, as the data now exist on 2 places (xls and ppt). Or, maybe I'm wrong?

Still need to do some testing tomorrow to see how it will all look when connected together.
I think this should work out fine in the end, but the only thing that is still bothering me is how to update ppt automatically when opened, without asking user if he/she wants to update the data.

[NeoPa]

Well, you've already discovered for yourself most of what I could have suggested. Well done. You also answered my questions intelligently and I think that helped (as indeed I had hoped).

The only issue I would comment on is that I would expect you could use Application Automation again to open the PPT document in PowerPoint and respond, as the user, to the update prompt. I'm not sure how that would work as I don't use PowerPoint except when I'm sent presentations, but from your earlier responses I expect just a pointer in the right direction will be enough to allow you to discover the full solution.

Welcome to Bytes!

[doma23]

@NeoPa
Thank you, you for sure helped!

[NeoPa]

Very pleased to hear that Doma :)

It's a pleasure dealing with someone who needs so little in the way of direction.