error using macro to copy from excel to ppt

Microsoft Excel 2003 (full product)
September 16, 2009 at 01:07:50
Specs: Windows XP Pro SP3, 980/0,99GB
Hi there,

Could anyone help me maybe with the following. I´m trying to copy paste a lot of graphs from excel to ppt with a macro i found somewhere else on this forum. It´s:

Sub CreateNewPowerPointPresentation()
' to test this code, paste it into an Excel module
' add a reference to the PowerPoint-library this is done from the Tools ---> References menu path and you
'need to find the microsoft powerpoint check box and check it. Then excel can use ppt objects within itself
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim i As Integer, strString As String
Dim Graphcount As Integer

Count = 108 'initialise count variable
i = 1
'returns the number of charts on the sheet at the time the macro is run.
'User customises the worksheets name to sheet that holds all the charts
Graphcount = Worksheets("Graficos").ChartObjects.Count

Set pptApp = CreateObject("PowerPoint.Application")
Set pptPres = pptApp.Presentations.Add(msoTrue) ' create a new presentation
' or open an existing presentation
' Set pptPres = pptApp.Presentations.Open("C:\Foldername\Filename.ppt")

Do While i < Graphcount ' starts a loop to copy charts

ActiveSheet.ChartObjects(i).Activate ' selects the chart object by its index number

With pptPres.Slides
Set pptSlide = .Add(.Count + 1, ppLayoutTitleOnly) ' add a slide
End With
With pptSlide
.Shapes(1).TextFrame.TextRange.Text = "Slide Title" 'edit to put a generic title on each slide or
' take this line out if you dont want a generic slide title to appear on each slide
.Shapes.PasteSpecial ppPasteDefault
With .Shapes(.Shapes.Count) ' sizes the graph on the slide
.Left = 120
.Top = 125.125
.Width = 480
.Height = 289.625
End With
End With

Application.CutCopyMode = False ' end cut/copy from Excel
Set pptSlide = Nothing
i = i + 1 ' increment the graph count to copy the next chart on the excel sheet

On Error Resume Next ' ignore errors

On Error GoTo 0 ' resume normal error handling
Set pptPres = Nothing

pptApp.Visible = True ' display the application
'pptApp.Quit ' or close the PowerPoint application
Set pptApp = Nothing

End Sub

Now , when running this macro i get the following error: automation error: library not registered

When pressing debug the following line is highlighted:

Set pptApp = CreateObject("PowerPoint.Application")

Does anyone have any idea what this might be? I already have checked the powerpoint library in the tools->reference menu of visual basic.

Many thanks, you would save my day!

See More: error using macro to copy from excel to ppt

Report •

September 16, 2009 at 06:25:09
I tried this macro and it didn't fail at the create object step.

This step fails if the appropriate library isn't referenced.

You say that you have checked the reference:
As you are using Office 2003, it should be:
Microsoft PowerPoint 11.0 ObjectLibrary

Make sure that it is loaded for your project.
Get your macro visible in the VBA and click somewhere in the macro.
Then Tools - References, and check the Library mentioned above.

To see if this works, go to the line
Set pptPres = pptApp.Presentations.Add(msoTrue)
backspace to remove the period between pptApp and Presentations
Now enter the period - does a list appear with Presentations selected?

I also assume that PowerPoint is loaded and opens OK from a normal Program menu or desktop Icon

Once you have this running you will need to change the following statement
Do While i < Graphcount
Do While i <= Graphcount
otherwise you will miss the last chart.


Report •

September 16, 2009 at 06:32:10
Sorry to tell you this, but your code did not produce any errors when I tried it.

I added the reference to PowerPoint as you instructed, I changed the name of a worksheet to be Graficos and I placed 3 charts in the worksheet.

The code seemed to run fine, except that it only copied 2 charts because of the line Do While i < Graphcount. Once i gets incremented to 3 (in my test), it is no longer less than Graphcount, so it bounces out of the loop. As written, it's always going to copy 1 less than actual number of charts.

In addition, I don't know what Count = 108 is for. The variable gets initialised but never gets used anywhere in the code.

Report •

September 16, 2009 at 06:38:09
Hi Humar,

Thanks for your answer. I took the steps you mentioned in your reply. The Microsoft PowerPoint 11.0 ObjectLibrary was already checked as a reference.

When taking away and then putting again the period indeed a list appears and presentations is selected in this list. Although when i try to run the macro it still gives me the same error.

Powerpoint 2003 is installed and opens normally, whether is is through the desktopicon or the start menu.

Any idea what could be wrong?


Report •

Related Solutions

September 16, 2009 at 07:02:47

I am not sure.

I have the following other libraries checked (not sure if they are all needed, but it's what I have)
Microsoft Office 11.0 ObjectLibrary
OLE Automation
Microsoft Excel 11.0 ObjectLibrary
Visual Basic For Applications

Also make sure that there are no Library entries marked MISSING


Report •

September 16, 2009 at 07:11:36

I have the same libraries checked, so i guess this can´t be it. I tried the macro on another computer and it worked there. I think i´ll re install excel and powerpoint to see if that does any good.

thanks again,

Report •

September 16, 2009 at 08:36:29
What about the fact that it copies 1 less chart than is present in the Excel spreadsheet?

Report •

September 16, 2009 at 08:45:11
Well, since it wasn't working at all i wasn't aware of that and i don't really know how to change that. As i said i copied the macro from somewhere else and only changed the bits that were necessary. Anyway, on a total of 100 graphs i don't really mind to do the last one by hand;)

Report •

September 16, 2009 at 08:59:56
Change that line to read: Do Until i > Graphcount and it should copy them all.

Report •

September 16, 2009 at 09:09:14
Perfect, thanks!

Report •

Ask Question