PowerPoint Automation from MS Access....queries to chart?

Question

Hey guys! I am wondering if there is anyone out there that can help me with this...

I have an Access db that I use to track metrics where I work and "number crunch" for data that I use to build ppt presentations. I have to usually do about 40 ppt's per month, and they are 98% charts.

Right now, I run queries one at a time (using SQL statements), take the resulting data and copy and paste it into and excel template (simply i made a mock table in this "template" so that the chart is already built and formatted), then copy the chart as a picture into a ppt template.

So there is a lot of manual work, which would not be that bad only I have a tone of these to do in a month's time.

SO.....how can I first of all run multiple queries in Access with VBA on the same dataset/table (I have to do sales by quarter, by month, by region, by state, by site...and all of these are Top5 aggregate, hence the reasons for the charts), and then send the resulting data to an specific excel workbook, while define what goes into what cell range???

If I get all the data into excel, and have the charts ready to go, then is there some VBA that will take the charts from excel (activeworksheet) and paste them into powerpoint as pictures in a quad view layout?

Can I do the same thing with an Access to PowerPoint approach and cut out excel all together?

I am a novice at best! ANY and ALL help, tips, advice is greatly appreciated!

Answer 1

Since you are a novice, perhaps you should break the task down into parts and automate the parts one at a time. Each step will provide benefits (i.e. time savings) and you can learn as you go.

It is hard to make specific recommendations based upon lack of specific information (what version etc.). That having been said, perhaps a good first step would be to link the Excel tables to the access queries so that the spreadsheets can auto-update every month and you will not have to cut and paste data from Access into Excel. You can do this linking entirely within Excel.

If you are using Excel 2007 click on "Data" in the Ribbon and then click on "From Access".

Answer 2

What you're asking is a lot of work:

Via VBA you'd have to open Excel (Excel Application manipulation from Access) , update your charts (Range manipulation, Data Update) if you have the rights then I would suggest having your pivot charts connected to the Access data and not pasted into the workbook, nevertheless I've been in enough situations where that was not possible. Then you would have to open your PowerPoint presentation and copy from the Excel to the PowerPoint. I've done all of these and know how much work it can save by creating a macro (via VBA) to do this. It's a lot of code.

Answer 3

actually the import from access to excel method in the ribbon will work for me perfectly! I would love to learn the code and general familiarity with automation...for a host of applications. but it seems impossible to find help/example of where to start online...

Anyone know where I could possibly start learning about this?

One thing I need to do is create a form in access that will create a powerpoint presentation.

Thanks guys! I do appreciate it!

Answer 4

hey this was working great but I hit a snag. I am using MSO 2003, and I have a database where I run mutiple queries from. Then I wanted to link a spreadsheet to the database and pull results from a few different queries into specific ranges. It was working fine by just selecting Data > Import Data > and then selecting the location of the database. It was then throughing up a selection window that allowed me to select from a list of objects in the database (both tables and queries).

now, all of a sudden when I try to use this method it only popluates tables, and there are no queries (though I know I have plenty of queries in the db). And if I select a table it just transfer that entire dataset to the spreadsheet.

I really need to be able to do this because I use that spreadsheet as a template for creating charts. All those queries are aggregate TOP 5 queries, and the auto refresh upon open is what was making this a very useful solution for me!

thanks for any and all advice!