Labeling PPT slides in VBA

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

hermes829Asked:
Who is Participating?
 
statler01Connect With a Mentor Commented:
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
 
statler01Commented:
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
 
hermes829Author Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
statler01Commented:
When you ran the page deleter,  did you also run the page labeler (or else define the variable 'Effective')?
0
 
hermes829Author Commented:
So I first ran the page labeler sub and then I ran the page deleter sub.
0
 
statler01Commented:
I just tested the page deleter code...
As long as the variable 'Effective' is defined, it works just fine.
0
 
statler01Commented:
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
 
hermes829Author Commented:
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
 
hermes829Author Commented:
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
 
hermes829Author Commented:
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
 
statler01Commented:
Easy problem you're having...

All you have to do is change "Dim Effective As Integer" to "Dim comimp As Integer".
0
 
hermes829Author Commented:
When I change that I get the same error message back. "Bad argument type. Expected collection index (string or integer)."
0
 
hermes829Author Commented:
That last line of text seemed to do it, thank you very much for your help and patience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.