Re: Excel 2003 VBProject Missing reference

Thanks Peter,
You have obviously put a lot of thought ino my issue and it is much
appreciated.

I am not up to speed on Late / Early binding but you have prompted me to now
cacth up.
--
Regards
John Howard
Sydney, Australia


"Peter T" wrote:

I can't comment on your particular case, except to say in some scenarios it
can be difficult if not impossible to remove a missing reference,
particularly programmatically (impossible if the security setting in user's
Excel does not allow Trust access to VB project).

When it's not possible to compile the project in the lowest version of any
user it's always best avoid the issue altogether, remove the reference your
end and convert to Late Binding as Barb suggested.

In general that means doing two things
- Change all object declarations from
Dim ppObj As Some-PP-Object-Type
to
Dim ppObj As Object

and do similar with any procedure arguments

The other thing is to replace any named pp constants with their intrinsic
values. If you have many, declare them as public constants at the top of a
normal module, and leave them as is in your code, eg

Public Const ppActionEndShow As Long = 6

To the constant values, in a project that includes the reference look them
up in Object Browser F2,
or in the immediate window
?ppActionEndShow hit enter
or in the existing code rt-click the constant and "Quick Info"

Head all your module Option Explicit and to a Tools/Compile to highlight any
undeclared variables.

Regards,
Peter T


"John Howard" <John.Howard@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8ABB948A-3089-41AF-A52B-5C5790A6BCF2@xxxxxxxxxxxxxxxx
Hi Barb,

Thanks for you prompt response.
I think however that we might be at cross purposes.
I am not up with Late / Early binding (a bit advanced for me), but I
gather
your code just determines the presence or not of Powerpoint on the local
machine.

What I am trying to do is programaitcally deslect the "MISSING: Powerpoint
12.0" entry in the VBProject References drop down list.

--
Regards
John Howard
Sydney, Australia


"Barb Reinhardt" wrote:

I'm just starting to look at this myself, but I think that using late
binding
might solve your problem. I just "threw" this together and didn't set
the
ref for PowerPoint. I ran this in Excel. I think it's probably easier
to
develop with early binding and then convert to late binding.

Sub test()
Dim PPTApp As Object

On Error Resume Next
Set PPTApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0

If PPTApp Is Nothing Then
Set PPTApp = CreateObject("PowerPoint.application")
End If
Debug.Print PPTApp.Name

PPTApp.Quit

End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"John Howard" wrote:

Within Excel 2003 this macro:

Private Sub Workbook_Open()

Dim wkBook As Workbook
Dim refCurr As Object
Dim i As Integer

Set wkBook = ThisWorkbook

'Deselects 'Missing'(IsBroken) references from the VBProject
References
dropdown list.
For i = wkBook.VBProject.References.Count To 1 Step -1
Set refCurr = wkBook.VBProject.References(i)

If refCurr.IsBroken Then
wkBook.VBProject.References.Remove refCurr
End If
Next

End Sub

fails to remove the VBProject Reference
MISSING: Microsoft Powerpoint 12.0 Object Library.
and returns Runtime Error 48, Error in loading DLL.

What code will programatically remove the missing reference?

The workbook was originally developed in Excel 2007 xlsm format and
will
continue to be distributed far and wide with instruction to Save As an
xls
file if pre 2007 versions are to be used.

I am understanably averse to having users unprotect the VB Editor to
allow
manual reference removal.

And so wil be most grateful for any forthcoming solution.

Thanks in anticipation

--
Regards
John Howard
Sydney, Australia



.

Relevant Pages

  • RE: Excel 2003 VBProject Missing reference
    ... I'm just starting to look at this myself, but I think that using late binding ... Set PPTApp = CreateObject ... End Sub ... What code will programatically remove the missing reference? ...
    (microsoft.public.excel.programming)
  • Re: Mailmerge from a button in excel
    ... This is called late binding. ... (When you use the reference, ... If you don't have a reference to MSWord, then excel doesn't have any idea what ... Private Sub Document_Open ...
    (microsoft.public.excel.programming)
  • Re: VBScript to VBA?
    ... Sub TabletoFeatureClass() ... Your VBScript uses late binding, ... of the VBE window. ... Once you've created a reference, ...
    (microsoft.public.access.modulesdaovba)
  • RE: Excel 2003 VBProject Missing reference
    ... I am not up with Late / Early binding, ... Set PPTApp = CreateObject ... End Sub ... 'Deselects 'Missing'references from the VBProject References ...
    (microsoft.public.excel.programming)
  • RE: Maintaining Maximum Platform Compatibility
    ... Early binding is tied to the exact reference that you select in the ... In this case Late Binding is really the only good option. ... So this would solve compatibility issues: ... Dim appWord As Word.Application ...
    (microsoft.public.excel.programming)
Quantcast