Macros fail due to missing libraries

[MJH1974]

Hi all

I work for a company in the UK and we report using an Excel workbook that runs a macro to export the "report" tab into Powerpoint. This allows all our projects to report on the same template for consistency.

Recently the person who introduced the Workbook has left the company and I've been asked to look after it - I'm pretty good with Excel but have no VBA skills to speak of.

The workbook was written using Excel 2003. However, some of our team use Excel 2007. This causes errors where the Powerpoint macro breaks ("Cannot find object or library").

Through Google, and trial and error, I have worked out that I need to uncheck the "Missing: Powerpoint 12.0" library and check the "Microsoft Powerpoint 11.0 library" and this allows the Powerpoint macro to work again.

I can continue to fix the problem but our user community are getting a bit frustrated. Is there any way to make the references dynamic so they work for Excel 2003 and 2007 users and the workbook doesn't break in the first place?

Thanks in advance,

Mark

[Norie]

Mark

Use late binding instead of early binding, then you won't need the library reference.

[MJH1974]

Thanks for your reply. Trouble is, as noted, I really don't have VBA skills so is "late binding" something which is easy to do? Do you please know of any resource(s) I can look at to help understand this further?

[MJH1974]

I've just checked the code in "ThisWorkbook" and it looks like Late Binding is already being used...


Sub Save_to_Powerpoint()

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim oPP As Object


Please excuse my ignorance if this isn't correct...

The problem seems to occur when an Excel 2007 user has saved the workbook and an Excel 2003 user opens the workbook afterwards. Something happens in the background to associate the Powerpoint library as 12.0 - my understanding is Office 2003 doesn't have 12.0, only 11.0. This is when I have to go into the References list, deselect 12.0 and add in 11.0.

Does this make any sense?

[JosephP]

no-that's early binding. late binding would be

Please Login or Register  to view this content.

you will also need to declare any constants from the PowerPoint library that you use and use createobject rather than New when initializing the objects-for instance

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

(the code may already use createobject)

[Norie]

It's relatively easy.

I don't have any links to hand but I'll see what I can find.

In the meantime if you posted some of the code you have I could show you the basics.

[MJH1974]

Thank you both so much, this is really helpful. I really am a novice with VBA but this has been assigned to me as the creator left the company and I'm struggling to make sense of it. I will tinker a bit this afternoon and see where I get to. Have a good weekend, thanks again.

[MJH1974]

Hi, it's me again

Thank you for your help and advice. I think I have sorted this out now after quite a lot of head-scratching and general confusion... it's probably not that elegant but it seems to work.


Sub Save_to_Powerpoint()

Dim PPApp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim oPP As Object

'New presentation
Set oPP = CreateObject("powerpoint.application")

'Reference active presentation
Set PPApp = GetObject(, "Powerpoint.Application")

'Copy Stat_Rept data as picture
Range(ActiveSheet.name & "!Print_Area").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

'add new presentation,make A4 blank & Paste the range
PPApp.Presentations.Add
PPApp.Visible = msoCTrue
PPApp.ActivePresentation.Slides.Add PPApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
PPApp.ActivePresentation.PageSetup.SlideSize = ppSlideSizeA4Paper
oPP.ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile

' Align the pasted range
With PPApp.ActiveWindow.Selection.ShapeRange
.Height = 750
.Width = 780
.Align msoAlignleft, True
.Align msoAlignMiddles, True
.Fill.ForeColor.SchemeColor = ppBackground
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.Transparency = 0#
End With

'Clean Up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

[Norie]

The code would look better if you wrapped it in code tags.

[MJH1974]

So I thought I'd fixed it with Late Binding, but haven't. Or maybe I just did it wrong.

The problem is a regular failure due to a missing Powerpoint Library (12.0) when an Excel 2007 user updates the spreadsheet and then an Excel 2003 user tries the Powerpoint macro.

Can I stop the spreadsheet creating a link to a 12.0 library file or is this down to Excel version compatibility?

Please help if you can - much appreciated.

Please Login or Register  to view this content.

[JosephP]

you need to remove the reference to the powerpoint library and save the file but first you need to declare the constants you are using

Please Login or Register  to view this content.

[MJH1974]

Thank you - I will try this afternoon.

[MJH1974]

Hi again

The code still throws up the same error: "COMPILE ERROR: CAN'T FIND PROJECT OR LIBRARY"

The error is triggeered when a Workbook has been used by an Excel 2007 user, and the Workbook has therefore created an association with the Microsoft Powerpoint 12.0 library (missing for Excel 2003 users).

I can work around it by changing the Reference to Microsoft Powerpoint 11.0 for Excel 2003 users and it works fine, but is there any way to stop the Workbook linking to specific Powerpoint libraries?

Please Login or Register  to view this content.

[JosephP]

you need to remove the reference to the powerpoint library and save the file

if you do that there can be no reference conflict