B
Brett Ellingson
Hello all,
Sorry for such a long, long post.
I have a problem with a macro that was created based on code from this
faq:
http://www.rdpslides.com/pptfaq/FAQ00326.htm
This set of code is below called the FIRST SET OF CODE
The second set of code is similar but instead of grouping or
ungrouping, it simply selects the OLE object of each slide so that the
data from an open excel spreadsheet is updated.
*********************FIRST SET OF CODE************
Dim oSlides As Slides
Dim oSld As Slide
Dim oShapes As Shapes
Dim oShp As Shape
Dim oShapeRange As ShapeRange
Dim oLineShp As Shape
Dim oEmbedShp As Shape
Dim oEmbedShpName As String
Dim i As Integer
Dim oGroupOrUngroup As Boolean
oGroupOrUngroup = False
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
ActiveWindow.View.GotoSlide Index:=oSld.SlideIndex
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoLine And oShp.Line.ForeColor.RGB =
16777215 Then
Set oLineShp = oShp
End If
If oShp.Type = msoEmbeddedOLEObject Then
Set oEmbedShp = oShp
oEmbedShpName = oShp.Name
End If
If oShp.Type = msoGroup Then
For i = 1 To oShp.GroupItems.Count
If oShp.GroupItems(i).Type = msoEmbeddedOLEObject
Then
Set oShapeRange = oShp.Ungroup
Exit For
End If
Next i
End If
Next oShp
If oEmbedShpName <> "" Then
******Set oShapeRange =
ActiveWindow.Selection.SlideRange.Shapes.Range _
(Array(oEmbedShp.Name, oLineShp.Name))
oShapeRange.Group.Select
End If
oEmbedShpName = ""
Next oSld
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
ActiveWindow.View.GotoSlide Index:=oSld.SlideIndex
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoGroup Then
For i = 1 To oShp.GroupItems.Count
If oShp.GroupItems(i).Type = msoEmbeddedOLEObject
Then
ActiveWindow.Selection.SlideRange.Shapes(oShp.Name).Select
ActiveWindow.Selection.ShapeRange.ZOrder
msoSendToBack
oGroupOrUngroup = True
Exit For
End If
Next i
End If
Next oShp
Next oSld
ActiveWindow.View.GotoSlide Index:=1
If oGroupOrUngroup Then
MsgBox ("You have just groupped the worksheets. Have a good
day!")
Else
MsgBox ("You have just ungroupped the worksheets and they are
ready for updating. Have a good day!")
End If
End Sub
*******************************SECOND SET OF CODE******************
Sub Update()
Dim Response
Response = MsgBox("Are all your Excel files for updating your
charts open?", vbYesNo + vbDefaultButton1)
If Response = vbNo Then
Exit Sub
End If
Dim oSlides As Slides
Dim oSld As Slide
Dim oShapes As Shapes
Dim oShp As Shape
Dim oShapeRange As ShapeRange
Dim oUpdate As Boolean
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
ActiveWindow.View.GotoSlide Index:=oSld.SlideIndex
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoEmbeddedOLEObject Or oShp.Type =
msoLinkedOLEObject Then
oUpdate = True
*****ActiveWindow.Selection.SlideRange.Shapes(oShp.Name).Select
ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb
Index:=1
ActiveWindow.Selection.Unselect
End If
Next oShp
Next oSld
ActiveWindow.View.GotoSlide Index:=1
If oUpdate = True Then
MsgBox ("The charts are updated!")
Else
MsgBox ("The charts have not been updated. Did you remember
to ungroup them?")
End If
End Sub
I've had this problem for about a month and a half and have read and
read faq's, newsgroup posting, help files on powerpoint and have had
no luck yet. The problem is that I get the following error:
**********************************
run-time error '=-2147024809 (80070057)':
The item with the specified name wasn't found.
*********************************
When I debug the code, it takes me to the following places that I
marked on the above code with asterisks in front of the line that
seems to give the error.
What is weird is that the errors are not consistent. The powerpoint
files have 72 or so slides in them and sometimes the macro will go
through the ppt with no problem or sometimes, it will do the first few
slides.
I've found a work-around that seems to work. Whatever slide that the
macro stops on, I select that slide, do a cut and paste of it into the
exact same location and then it seems to work fine. Then what I do is
go back to the first slide, select the slide and run the macro again,
then it will run fine at least up until it gets past the slide I just
copied and pasted. Then it will either keep going and running fine or
it will stop again on another seemingly random slide. If I cut and
past that slide and run the macro again it will run fine until it hits
the next slide just past the one I fixed a second ago. As a note, I
only use this work around on the update macro, because if the grouping
macro gets and error, I have to go back and manually ungroup or
regroup the stuff.
Does anyone have an idea of how to help solve this problem? Feel free
to e-mail me back at the e-mail address too and hopefully this is
clear as mud, if you need more of an explanation please let me know.
Brett Ellingson
Sorry for such a long, long post.
I have a problem with a macro that was created based on code from this
faq:
http://www.rdpslides.com/pptfaq/FAQ00326.htm
This set of code is below called the FIRST SET OF CODE
The second set of code is similar but instead of grouping or
ungrouping, it simply selects the OLE object of each slide so that the
data from an open excel spreadsheet is updated.
*********************FIRST SET OF CODE************
Dim oSlides As Slides
Dim oSld As Slide
Dim oShapes As Shapes
Dim oShp As Shape
Dim oShapeRange As ShapeRange
Dim oLineShp As Shape
Dim oEmbedShp As Shape
Dim oEmbedShpName As String
Dim i As Integer
Dim oGroupOrUngroup As Boolean
oGroupOrUngroup = False
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
ActiveWindow.View.GotoSlide Index:=oSld.SlideIndex
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoLine And oShp.Line.ForeColor.RGB =
16777215 Then
Set oLineShp = oShp
End If
If oShp.Type = msoEmbeddedOLEObject Then
Set oEmbedShp = oShp
oEmbedShpName = oShp.Name
End If
If oShp.Type = msoGroup Then
For i = 1 To oShp.GroupItems.Count
If oShp.GroupItems(i).Type = msoEmbeddedOLEObject
Then
Set oShapeRange = oShp.Ungroup
Exit For
End If
Next i
End If
Next oShp
If oEmbedShpName <> "" Then
******Set oShapeRange =
ActiveWindow.Selection.SlideRange.Shapes.Range _
(Array(oEmbedShp.Name, oLineShp.Name))
oShapeRange.Group.Select
End If
oEmbedShpName = ""
Next oSld
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
ActiveWindow.View.GotoSlide Index:=oSld.SlideIndex
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoGroup Then
For i = 1 To oShp.GroupItems.Count
If oShp.GroupItems(i).Type = msoEmbeddedOLEObject
Then
ActiveWindow.Selection.SlideRange.Shapes(oShp.Name).Select
ActiveWindow.Selection.ShapeRange.ZOrder
msoSendToBack
oGroupOrUngroup = True
Exit For
End If
Next i
End If
Next oShp
Next oSld
ActiveWindow.View.GotoSlide Index:=1
If oGroupOrUngroup Then
MsgBox ("You have just groupped the worksheets. Have a good
day!")
Else
MsgBox ("You have just ungroupped the worksheets and they are
ready for updating. Have a good day!")
End If
End Sub
*******************************SECOND SET OF CODE******************
Sub Update()
Dim Response
Response = MsgBox("Are all your Excel files for updating your
charts open?", vbYesNo + vbDefaultButton1)
If Response = vbNo Then
Exit Sub
End If
Dim oSlides As Slides
Dim oSld As Slide
Dim oShapes As Shapes
Dim oShp As Shape
Dim oShapeRange As ShapeRange
Dim oUpdate As Boolean
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
ActiveWindow.View.GotoSlide Index:=oSld.SlideIndex
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoEmbeddedOLEObject Or oShp.Type =
msoLinkedOLEObject Then
oUpdate = True
*****ActiveWindow.Selection.SlideRange.Shapes(oShp.Name).Select
ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb
Index:=1
ActiveWindow.Selection.Unselect
End If
Next oShp
Next oSld
ActiveWindow.View.GotoSlide Index:=1
If oUpdate = True Then
MsgBox ("The charts are updated!")
Else
MsgBox ("The charts have not been updated. Did you remember
to ungroup them?")
End If
End Sub
I've had this problem for about a month and a half and have read and
read faq's, newsgroup posting, help files on powerpoint and have had
no luck yet. The problem is that I get the following error:
**********************************
run-time error '=-2147024809 (80070057)':
The item with the specified name wasn't found.
*********************************
When I debug the code, it takes me to the following places that I
marked on the above code with asterisks in front of the line that
seems to give the error.
What is weird is that the errors are not consistent. The powerpoint
files have 72 or so slides in them and sometimes the macro will go
through the ppt with no problem or sometimes, it will do the first few
slides.
I've found a work-around that seems to work. Whatever slide that the
macro stops on, I select that slide, do a cut and paste of it into the
exact same location and then it seems to work fine. Then what I do is
go back to the first slide, select the slide and run the macro again,
then it will run fine at least up until it gets past the slide I just
copied and pasted. Then it will either keep going and running fine or
it will stop again on another seemingly random slide. If I cut and
past that slide and run the macro again it will run fine until it hits
the next slide just past the one I fixed a second ago. As a note, I
only use this work around on the update macro, because if the grouping
macro gets and error, I have to go back and manually ungroup or
regroup the stuff.
Does anyone have an idea of how to help solve this problem? Feel free
to e-mail me back at the e-mail address too and hopefully this is
clear as mud, if you need more of an explanation please let me know.
Brett Ellingson