Using Excel and Access data in PowerPoint Tables (by Brian Reilly and Naresh Nichani)

Do you have data in Excel or Access that you would like to use in PowerPoint tables?

This tutorial will give several examples of how to do this using VBA code.

This example shows how to place the code in PowerPoint, retrieve data from Excel and or Access and display it in a PowerPoint table, but you can adapt the same methods to using Excel or Access data for other purposes in your PowerPoint presentations or add-ins. Better yet, you can adapt the same techniques to any other application that supports VBA or even to Visual Basic projects.

We previously published an example of how to retrieve data from Excel or Access in pptfaq.com/FAQ00795.htm . This is an improvement on that method which worked only in Slide View and not in SlideShow View. This example will work either both Slide View and SlideShow View.

The accompanying example file is here: Rightclick and choose Save target As to download DisplayData-2.zip

The example opens up a wizard-like form which lets you browse for an Excel or Access file.

After browsing for the file click the "Open" button to fill the browse text box on the form. Then click the "Next" button.

The form will populate the left hand side list box with a list of all tables in the Access file or all worksheets in the Excel file. Choosing one of the tables or worksheets will poplate the right hand side listbox with the available fields or column labels. Checkboxes permit you to choose which items you want to show in your table.

Click "Next" to choose how many records per page you want to show in the PowerPoint table. And you can change the Title of the table here as well.

Then click "Finish" and the table will be created.

The code lets you choose how to format the fonts and fills in the table. Look in the function "CreateSlide" (search for ".Fill" in the code).

I have made the fonts Arial white and made the fill a variation on red, but you can change the formatting to whatever suits your needs.

        With oShapeInsideTable.Fill
            .Solid
            .ForeColor.RGB = RGB(227, 37, 51)
        End With

There are other settings for line height etc. The code is provided in unprotected format and most of the working code is assigned to the userform and completely viewable in the VBE.

Good luck and feel free to post questions in the PowerPoint Newsgroup.

---