?
Solved

Labeling PPT slides in VBA

Posted on 2009-04-21
13
Medium Priority
?
758 Views
Last Modified: 2013-11-25
So I generate a report on a powerpoint template with almost 150 slides. The data is brought in from excel and when I first made the macro I didn't bother to actually label the ppt slides and then use that label as a reference for where certain values on my excel sheet should be brought in. Instead my excel to ppt macro just brings in data from excel to slide 10 and then 11, etc.

As I have done this report for awhile now some slides are no longer relevant but I still have to have them in there because if they're not then excel will bring in data to the wrong slide. So the way that I've been handling it is that after the data is brought in I just disconnect the ppt from the excel file and then delete the slides that are no longer relevant and then move around the slides whose order has changed. In order to automate this process and make this part of the report creation process more flexible I was hoping to come up with a macro that would label the slides in VBA. What I've found online so far is below. What I was now wondering is how I would reference effective later on in the VBA module. So for example in Sub Page deleter, I am currently using the activewindow.viiew.gotoslide index formula, but I was hoping that there is a formula which would reference the tag I gave to slide 8 instead.
Sub Page_Labeler()
 
With ActivePresentation.Slides(8)
.Tags.Add "effective", "6"
End With
 
End Sub
 
Sub Page_deleter()
 
    ActiveWindow.View.GotoSlide Index:=6
    ActiveWindow.Selection.SlideRange.Delete
 
End Sub

Open in new window

0
Comment
Question by:hermes829
  • 7
  • 6
13 Comments
 
LVL 9

Expert Comment

by:statler01
ID: 24196622
Is there any other reason for use of the tag?

From what you've posted here, I would use an integer variable instead of a tag...
(I've modified the code that you put up, I'm assuming that there's more than this, because this doesn't really make sense by itself...)

The following is untested:

Sub Page_Labeler()
    Dim Effective As Integer
 
    With ActivePresentation.Slides(8)
        Effective = 6
    End With
End Sub
 
Sub Page_deleter()
    ActivePresentation.Slides(Effective).Delete 
End Sub

Open in new window

0
 

Author Comment

by:hermes829
ID: 24196756
There's about 150 slides that I'll have to label, I was just showing an example of what I thought about doing.

The page labeler sub seemed to work fine, but when I ran the page deleter sub I got this error: "Run-time error '-2147024809 (80070057)': Slides (unknown member) : Bad argument type. Expected collection index (string or integer).

0
 
LVL 9

Expert Comment

by:statler01
ID: 24197593
When you ran the page deleter,  did you also run the page labeler (or else define the variable 'Effective')?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:hermes829
ID: 24197763
So I first ran the page labeler sub and then I ran the page deleter sub.
0
 
LVL 9

Expert Comment

by:statler01
ID: 24197837
I just tested the page deleter code...
As long as the variable 'Effective' is defined, it works just fine.
0
 
LVL 9

Expert Comment

by:statler01
ID: 24198121
If you're willing to put up the rest of your code I might be able to help you clean it up and get the result you need.
0
 

Author Comment

by:hermes829
ID: 24198238
That's strange, I don't understand why it wouldn't be working for me. I just run the page labeler macro and then run the page deleter macro. Any ideas?
0
 

Author Comment

by:hermes829
ID: 24198284
The two subs are below. I've tried running it again but ended up with the same result.
Sub Page_Labeler()
    Dim Effective As Integer
 
    With ActivePresentation.Slides(13)
        comimp = 13
    End With
End Sub
 
Sub Page_deleter()
    ActivePresentation.Slides(comimp).Delete
End Sub

Open in new window

0
 

Author Comment

by:hermes829
ID: 24198488
One sidenote is that I am using powerpoint 2007 and that this file is in ppt 97-03 compatibility mode. I remember a code I was working on before didn't work because of that but just wanted to let you know in case a similar type of problem was occurring  now.
0
 
LVL 9

Expert Comment

by:statler01
ID: 24198577
Easy problem you're having...

All you have to do is change "Dim Effective As Integer" to "Dim comimp As Integer".
0
 

Author Comment

by:hermes829
ID: 24199169
When I change that I get the same error message back. "Bad argument type. Expected collection index (string or integer)."
0
 
LVL 9

Accepted Solution

by:
statler01 earned 2000 total points
ID: 24199299
I'm not sure if this is a 97-03 compatibility mode thing or not...

Unless there's more code in your with statement, it's really not needed.
Just for giggles, let's try running the code below:

That should help isolate where the problem is.
Sub Page_deleter()
    Dim comimp As Integer
    comimp = 13
    ActivePresentation.Slides(comimp).Delete
End Sub

Open in new window

0
 

Author Closing Comment

by:hermes829
ID: 31572810
That last line of text seemed to do it, thank you very much for your help and patience.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This video teaches viewers how to fit pictures into slides, crop and remove backgrounds, and alter photos to look more professional.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question