Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBProject Missing reference
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBProject Missing reference
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBProject Missing reference
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 VBProject Missing reference
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Business Explorer - Missing object or reference error | Excel Discussion (Misc queries) | |||
Missing help in Excel 2003 | Excel Programming | |||
Missing Reference in Excel 2003 | Excel Discussion (Misc queries) | |||
Missing function in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 VBA Help for TRanspose is missing | Excel Programming |