Export access form to ppt

[osmosisgg]

I am toying with the idea of exporting a form (which is by filter) into a ppt presentation. I am currently printing to pdf and there is nothing really wrong with it; other than the seeing half the page empty at the bottom. Anyhow, I found the below code on msdn microsoft. I attempted it with changing employees to match my table, and lastname to match the filtered field. This just gives me a ppt with Hi page 1 and the value within the filered field. (53 slides of this) So, is it possible that something like the below could work by using the form's layout (by the filtered product) without having to copy, paste and manipulate one by one in ppt?

Sub cmdPowerPoint_Click()
    Dim db As Database, rs As Recordset
    Dim ppObj As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    
    On Error GoTo err_cmdOLEPowerPoint
    
    ' Open up a recordset on the Employees table.
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
    
    ' Open up an instance of Powerpoint.
    Set ppObj = New PowerPoint.Application
    Set ppPres = ppObj.Presentations.Add
    
    ' Setup the set of slides and populate them with data from the
    ' set of records.
    With ppPres
        While Not rs.EOF
            With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
                .Shapes(1).TextFrame.TextRange.Text = "Hi!  Page " & rs.AbsolutePosition + 1
                .SlideShowTransition.EntryEffect = ppEffectFade
                With .Shapes(2).TextFrame.TextRange
                    .Text = CStr(rs.Fields("LastName").Value)
                    .Characters.Font.Color.RGB = RGB(255, 0, 255)
                    .Characters.Font.Shadow = True
                End With
                .Shapes(1).TextFrame.TextRange.Characters.Font.Size = 50
            End With
            rs.MoveNext
        Wend
    End With
    
    ' Run the show.
    ppPres.SlideShowSettings.Run
    
    Exit Sub
    
err_cmdOLEPowerPoint:
    MsgBox Err.Number & " " & Err.Description
End Sub

[zmbd]

SO, If I am following you, you'd like a single Powerpoint slide that acts like an Access form?

We also need to know which version of Office you are using as there are some drastic changes in the automation code between versions.

[osmosisgg]

Access 2010:

I was thinking to have each record to show on a slide, with the same format as the form and by the selected filter. So, if I selected TAC, all records from TAC would be in the ppt. There would be no empty white at the bottom as in the pdfs. It seems even though I have the margins set, etc...white takes up a lot of space.

Did you mean by "acting like a form" that others could edit the fields? That would be an intersting concept.

[zmbd]

I wouldn't let powerpoint act as the front-end to a database.
You can however set a reference in Powerpoint to the DAO model and then open a recordset based on a stored query (or for that fact, most likely a dynamic one).

I have a "connect-4/Blackout" style game I built in powerpoint for a class I teach to third year students. It was not the easiest thing to build as there isn't a lot of information on the internet about using VBA in powerpoint; however, I started out with the database concept until I ran into the fact that it would be running on both PC's and Macs - still doable, but a real pain.

So to the point:
Trying to get your Access form exported to powerpoint. I've not seen an easy method.

So a workaround... depending on your database file:
- If it is small then we could use the single slide approach where we open the recordset within powerpoint and then populate the slide controls with information from the database. We'd use some action buttons to tie into the "next" "previous" functionality (mind you, I'll be learning some of this with you at the same time (^_^) ) to move to the correct database record. If it's a small enough recordset - might just read into an array and step thru the array.

OR

- A variation on the code you already have in your post: instead of inserting a slide as in the code you have, start out with a single master slide formatted as you desire, then from within access, use the duplicate method ( Slide.Duplicate Method (PowerPoint) Office 2010 ) to duplicate this master slide for each record of interest and enter the record's information into the controls. There's a way to name each control and we refer to the slide by its index within the slides collection. So we duplicate the master, change it's controls displayed text, and move on. Can use the default slide P&N-Action buttons etc... If you start out with everything fairly close to begin with then you can finetune the presentation within PP.

IN either case, the code is going to be fairly complex, I'd say on a 10 point scale with People like Adezii, Rabbit, TheSmileyCoder, and last but not least Neopa closer to that 9/10 range, me around a 5 to 8 and then a 2nd year college Compsci around 4/5 - this rates around the 5 to 6 range.

[osmosisgg]

I wouldn't want anyone to be able to modify via ppt. Just a show n tell the data type of thing. Ppt by death HAHA! Is 53 records a small enough recordset? By filter there are 4 sections with 14, 2, 6 and 31 records.

If this is complex for y'all, then this is defintely way above me as I don't know much about sql/vba. I've been "learning on task" and am challenged by what is easy for most folks. I do have a lot of heart, I absorb quite a bit, and get through it, but unsure I know enough for this as complex as it sounds. I would need hand holding.

If you are willing to tackle it and have patience with me, I am good to try.

[zmbd]

K:

Let start out with getting that first slide to be properly formatted as in either way you want to go, death by a thousand slides or death by bludgening with the same slide, the method I suggested will depend on this slide.

Once you are happy with it then decide which way you want to run, with the recordset within PPT or the slide duplication via ACC.

Once you have these things done and the choice made, we'll proceed from there.

B.O.L

[osmosisgg]

:)

So, first slide I entered a title and subtitle.

Second Slide: absolutely blank (since the form already contains the section/poc- no need to put a title)

With the filter set to a section, I would like that recordset to go into the ppt.

[zmbd]

So, first slide I entered a title and subtitle.

Great, no problem

Second Slide: absolutely blank (since the form already contains the section/poc- no need to put a title)

If this is the slide we're going to display the data with, then you need to build this form. Populate it with the text boxes, animations etc... the more of the framework you have in place for this form the better.

We'll go in and name the controls after the slide is properly configured.

With the filter set to a section, I would like that recordset to go into the ppt

Yes, I got that point. We can do that a number of ways and the method will depend somewhat on which path you want to take... the death by slide engulfment or death by the record.

[osmosisgg]

Ok. I have slide 2 ready. It has 10 textboxes to display the data from access. There are other textboxes used as labels.

[zmbd]

Great,
In powerpoint 2010
Show the Slide we're going to use as or display master
Ribbon, Home Tab
Editing Group
Select drop down
Show selection pane

In the pane that shows up you can double click the name and change it to something useful. Say from "TextBox 1" to "txt_fname" etc... can't tell which is what, simple, on the slide simply double click into the textbox that you want to name... the index bar in the Selection pane will move to that item in the list.

We need to decide which direction to go.
This way we can refer to the controls via the name instead of the index.

Ok, things are about to get really busy here in the lab so be there will be a delay in replies for the near future... (^_^)

[osmosisgg]

Thank you for easy instructions. Done with that part.

I do appreciate your time. Really :) I understand busy.

[zmbd]

I haven't forgotten you. (^_^)
Really unusually busy day today in the lab.

The next step we need to look at is how to create the presentation.

So long as the database is available in the same folder/thumbdrive it should be easy to do and we'll move into the PPT side of the VBA, or if you would rather deal with just the PPT file then our next move will be into the Access side of VBA.

OK, I'll be offline at one of my remote labs for the next several hours.

[osmosisgg]

Yay, I wasn't forgotten ;)

I have them both in the same folder. In the future, I may have to upoload to a shared network drive.

Hope all went well at the remote sites.

[zmbd]

powerpoint
<alt><F11>
The VBA editor window will open.
Default should be a white pane on the left and a grey area on the right.
<Menu><Insert>{ Module}
A new white pane should open to the right and in the left pane the tree should have a new node named "Modules" with a branch named "Module1"
On the right, first line should be:
Option Explicit

If not insert it.
If not then also (you may want to check this anyway):
<Menu><Tools><O ptions>
[Editor tab]
Uncheck auto syntax check - error lines by default turn red anyway.
Checkmark everything else on this tab-page.
[General tab]
Error Trapping: Should be break on unhandled errors

select the following and copy, paste it in this module.

Sub zshowmenames()
    Dim zpres As Presentation
    Dim zslides As Slides
    Dim zslide As Slide
    Dim zshapes As Shapes
    Dim zshape As Shape
    Set zpres = ActivePresentation
    Set zslides = zpres.Slides
    Debug.Print "Slide Count: " & zslides.Count
    For Each zslide In zslides
        Set zshapes = zslide.Shapes
        Debug.Print String(20, "-")
        Debug.Print "Name", "SlideID", "SlideIndex", "SlideNumber"
        Debug.Print zslide.Name, zslide.SlideID, zslide.SlideIndex, zslide.SlideNumber
        For Each zshape In zshapes
            Debug.Print zshape.Name & ",";
        Next
        Debug.Print "..."
        Set zshapes = Nothing
    Next
    Set zslides = Nothing
    Set zpres = Nothing
End Sub

<menu><tools><d ebug>{Compile.. .}

SAVE THE FILE (^_^)
SaveAs a MACRO ENABLED file.

<ctrl><g>
In the immediate window that opens type the following and press enter: zshowmenames

You will something like:

zshowmenames
Slide Count: 44
--------------------
Name          SlideID       SlideIndex    SlideNumber
Slide1         256           1             1 
Title 1,Subtitle 2,Action Button: Forward or Next 4,TextBox 3,...
--------------------
Name          SlideID       SlideIndex    SlideNumber
Slide2         257           2             2 
Tile50,Tile40,Tile30,Tile20,Tile10,Tile00,Tile51,Tile41,Tile31,Tile21,Tile11,... 
(omitted remaining)

click in the immediate window
<ctrl><a>
<ctrl><c>

Come back to the post here, click the [CODE/] button,
and paste the selected text between the code tags.

Sounds like my remote lab needs another visit... sigh.
In the mean time I'll be thinking about the connection string to the database.

[osmosisgg]

Here ya go.

zshowmenames
Slide Count: 2
--------------------
Name          SlideID       SlideIndex    SlideNumber
Slide26        281           1             1 
Rectangle 4,Rectangle 5,Gruppieren 8,Text Box 3,Date Placeholder 7,Rectangle 4,...
--------------------
Name          SlideID       SlideIndex    SlideNumber
Slide36        291           2             2 
txtPOC,Picture 2,Rectangle 5,Rectangle 4,Picture 10,Label_title,txt_question,txt_q#,txt_num,txt_denom,txt_results,Label_Goal,txt_goal,Label_data,Label_PS,Label_CA,txt_PS,txt_CA,txt_Sec,...