Hello to everybody,
my name is Doma and I work as an intern in one banking group in Europe. This is my first post, so 'Hello world!'
Currently I'm working on a development of an access application that deals with some financial data. Here is my problem.
I need a button on access form that will 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.
This is the code behind the button click:
This is basically the code of the macro (transformed to VBA):
This is the sub which I'm calling to open ppt file:
Thank you in advance!
my name is Doma and I work as an intern in one banking group in Europe. This is my first post, so 'Hello world!'
Currently I'm working on a development of an access application that deals with some financial data. Here is my problem.
I need a button on access form that will 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.
This is the code behind the button click:
Code:
Private Sub cmdRunPpt_Click()
DoCmd.RunMacro "EXPORT_ROAC"
Call PPT_OPEN
End SubThis is basically the code of the macro (transformed to VBA):
Code:
DoCmd.OutputTo acQuery, "ROAC_Q", "MicrosoftExcelBiff8(*.xls)", "C:\ACCESS\DATA\ROAC.XLS", False, "", 0This is the sub which I'm calling to open ppt file:
Code:
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 SubThank you in advance!
