Solved

Deleting references

Posted on 2011-03-12
61
1,384 Views
Last Modified: 2013-12-04
I am trying to delete a broken reference in this VBA module but it gives the error

Object library not registered

I have found a long discussion here which does not help much.

http://www.eggheadcafe.com/software/aspnet/33065241/universal-problem--fixing-missing-office-references-programatical.aspx

I have a file which is updated by various users having different versions of excel and autocad and the references have to be manually updated every time the file is opened in a version different from the one in which it was saved.

Can someone show me a way to delete the broken reference so that I can add a working version - all programmatically?

Saqib
0
Comment
Question by:Saqib Husain, Syed
  • 31
  • 28
61 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I have some code I have used previously thouh on a quick test in Win 7 Office 2010 it doesn't seem to be working.  I suggest you try it and see.

BAsically it (used to) delete broken references then add the reference using the guid in the pre-populated array.  A list of the GUID's I have encountered is displayed as comments so cut and paste those you require.

Chris
Sub AddReference_Test()
Dim strGUID As Variant
Dim itm As Variant
Dim ref As Variant
Dim office_GUID As Variant
Dim i As Long
' Office C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' VBA C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL    {000204EF-0000-0000-C000-000000000046}
' Excel   C:\Program Files\Microsoft Office\Office12\EXCEL.EXE    {00020813-0000-0000-C000-000000000046}
' Word    C:\Program Files\Microsoft Office\Office12\MSWORD.OLB   {00020905-0000-0000-C000-000000000046}
' Access  C:\Program Files\Microsoft Office\Office12\MSACC.OLB    {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
' VBScript_RegExp_55  C:\Windows\system32\vbscript.dll\3  {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
' stdole  C:\Windows\system32\stdole2.tlb {00020430-0000-0000-C000-000000000046}
' VBIDE   C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB {0002E157-0000-0000-C000-000000000046}
' MSForms C:\Windows\system32\FM20.DLL    {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' Scripting   C:\Windows\system32\scrrun.dll  {420B2830-E718-11CF-893D-00A0C9054228}
' MAPI    C:\Program Files\Common Files\system\MSMAPI\2057\cdo.dll   {3FA7DEA7-6438-101B-ACC1-00AA00423326}
' ADODB   C:\Program Files\Common Files\System\ado\msado15.dll    {B691E011-1797-432E-907A-4D8C69339129}
' DAO C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll   {00025E01-0000-0000-C000-000000000046}
' ACRODISTXLib    C:\Program Files\ADOBE\DISTILLR\ACRODIST.EXE    {317DA881-ECC5-11D1-B976-00600802DB86}
' Powerpoint  C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE11\MSPPT.OLB    (91493440-5A91-11CF-8700-00AA0060263B}

    On Error Resume Next
    For i = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(i)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next i
    office_GUID = Array("{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}")
    For Each strGUID In office_GUID
        Err.Clear
        Application.VBE.ActiveVBProject.References.AddFromGuid _
        guid:=office_GUID.id, major:=0, minor:=0 ' 0:0 to use latest versions installed

         'If an error was encountered, inform the user
        Select Case Err.number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
    Next
    On Error GoTo 0
End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Note also if you select the references you do want on a PC then run the following code:

If you set the path to a valid path and the filename to whatever you want

    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"

Then in the file will be displayed the GUID strings you require

Chris
Sub whatRefs()
Dim FSO As Object
Dim outputFile As Object
Dim outputPath As String
Dim outputFileName As String
Dim ref As Long
Dim VBP As Object

    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"
    Set FSO = CreateObject("scripting.filesystemobject")
    If VBA.Right(outputPath, 1) <> "\" Then outputPath = outputPath & "\"
    On Error Resume Next
    If Not FSO.FolderExists(outputPath) Then
        FSO.CreateFolder outputPath
    End If
    Set outputFile = FSO.createtextfile(outputPath & outputFileName, True)
    If Application.Name = "Microsoft Excel" Then
        Set VBP = Excel_App_Tuning("thisObject")
    ElseIf Application.Name = "Microsoft Word" Then
        Set VBP = Word_App_Tuning("thisObject")
    ElseIf Application.Name = "Microsoft Access" Then
        Set VBP = Access_App_Tuning("thisObject")
    End If

    On Error Resume Next
    For ref = 1 To VBP.References.Count
        With VBP.References(ref)
            outputFile.Write "' " & .Name & " Library" & String(40 - Len(.Name), " ") & "  Major, " & .major & "  Minor, " & VBA.Right("  " & .minor, 2) & "     " & .guid & vbCrLf
        End With
    Next
    On Error GoTo 0
    outputFile.Close

End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
On a quick test on a new xp baseline and Win 7, there was a bug and an assumption so the following ones do seem ok.

Chris
Sub AddReference_Test()
Dim strGUID As Variant
Dim itm As Variant
Dim ref As Variant
Dim office_GUID As Variant
Dim i As Long
' Office C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' VBA C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL    {000204EF-0000-0000-C000-000000000046}
' Excel   C:\Program Files\Microsoft Office\Office12\EXCEL.EXE    {00020813-0000-0000-C000-000000000046}
' Word    C:\Program Files\Microsoft Office\Office12\MSWORD.OLB   {00020905-0000-0000-C000-000000000046}
' Access  C:\Program Files\Microsoft Office\Office12\MSACC.OLB    {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
' VBScript_RegExp_55  C:\Windows\system32\vbscript.dll\3  {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
' stdole  C:\Windows\system32\stdole2.tlb {00020430-0000-0000-C000-000000000046}
' VBIDE   C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB {0002E157-0000-0000-C000-000000000046}
' MSForms C:\Windows\system32\FM20.DLL    {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' Scripting   C:\Windows\system32\scrrun.dll  {420B2830-E718-11CF-893D-00A0C9054228}
' MAPI    C:\Program Files\Common Files\system\MSMAPI\2057\cdo.dll   {3FA7DEA7-6438-101B-ACC1-00AA00423326}
' ADODB   C:\Program Files\Common Files\System\ado\msado15.dll    {B691E011-1797-432E-907A-4D8C69339129}
' DAO C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll   {00025E01-0000-0000-C000-000000000046}
' ACRODISTXLib    C:\Program Files\ADOBE\DISTILLR\ACRODIST.EXE    {317DA881-ECC5-11D1-B976-00600802DB86}
' Powerpoint  C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE11\MSPPT.OLB    (91493440-5A91-11CF-8700-00AA0060263B}

    On Error Resume Next
    For i = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(i)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next i
    office_GUID = Array("{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", "{00020905-0000-0000-C000-000000000046}", "(91493440-5A91-11CF-8700-00AA0060263B}")
    For Each strGUID In office_GUID
        Err.Clear
        Application.VBE.ActiveVBProject.References.AddFromGuid _
        GUID:=strGUID, major:=0, minor:=0 ' 0:0 to use latest versions installed

         'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
                Stop
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
    Next
    On Error GoTo 0
End Sub

Sub whatRefs()
Dim FSO As Object
Dim outputFile As Object
Dim outputPath As String
Dim outputFileName As String
Dim ref As Long
Dim VBP As Object

    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"
    Set FSO = CreateObject("scripting.filesystemobject")
    If VBA.Right(outputPath, 1) <> "\" Then outputPath = outputPath & "\"
    On Error Resume Next
    If Not FSO.FolderExists(outputPath) Then
        FSO.CreateFolder outputPath
    End If
    Set outputFile = FSO.createtextfile(outputPath & outputFileName, True)
        Set VBP = Application.ThisWorkbook.VBProject

    On Error Resume Next
    For ref = 1 To VBP.References.Count
        With VBP.References(ref)
            outputFile.Write "' " & .Name & " Library" & String(40 - Len(.Name), " ") & "  Major, " & .major & "  Minor, " & VBA.Right("  " & .minor, 2) & "     " & .GUID & vbCrLf
        End With
    Next
    On Error GoTo 0
    outputFile.Close

End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Phooey! ... delete the stop command - that was only put there for testing purposes ALSO and very important you do need to ensure trust to the VB Project is given

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Hi, Chris,

The first one does not delete the broken reference.


The second and third ones do not run and give the error
cannot find project or library
if the reference is broken. It works fine if the reference is updated.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
What OS / Win ver?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Windows Xp
Office 2003
Autocad 2008

other machines have

Windows 7
office 2003 or 2007
Autocad 2008 or 2011
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I have just retested OK ... though i am having a general issues adding PPT that is application not VBA so I discount that from this issue.

Can you add the required references then run the what refs sub and provide what you het.

Now delete the extra refs from the install then try the third post, what exactly happens or does not happen?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
I tried adding the required reference by the simple routine

Sub SetRef()
Dim strGUID As String
strGUID = "{851A4561-F4EC-4631-9B0C-E7DC407512C9}"
ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, major:=1, minor:=0
End Sub

but it gave

Name conflicts with existing module, project, or object library
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
NO, can you forget the routine for a minute and try to add the reference(s) you are interested in manually from the VBE screen, (and here I was having some problems with Powerpoint).  Once you have added them manually run the what refs sub and return the results here.

If the reference does not add manually ten find a machine where itr does exist and run the whatrefs sub

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility

' VBA Library                                       Major, 4  Minor,  0     {000204EF-0000-0000-C000-000000000046}
' Excel Library                                     Major, 1  Minor,  5     {00020813-0000-0000-C000-000000000046}
' stdole Library                                    Major, 2  Minor,  0     {00020430-0000-0000-C000-000000000046}
' Office Library                                    Major, 2  Minor,  3     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' MSForms Library                                   Major, 2  Minor,  0     {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' AutoCAD Library                                   Major, 1  Minor,  0     {851A4561-F4EC-4631-9B0C-E7DC407512C9}

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Now that's a shock ... I was expecting something like:


' VBA Library                                       Major, 4  Minor,  1     {000204EF-0000-0000-C000-000000000046}
' Excel Library                                     Major, 1  Minor,  7     {00020813-0000-0000-C000-000000000046}
' MSForms Library                                   Major, 2  Minor,  0     {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' stdole Library                                    Major, 1  Minor,  0     {00020430-0000-0000-C000-000000000046}
' Office Library                                    Major, 2  Minor,  5     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}

Which app are you running it in?
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Excel 2003

I do not see what is it that seems shocking.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Even if somehow your install does not return the major, minor and GUID the script generates constanys for the text Major/Minor/Library and GUID whereas all you seem to get is the library name and constant.

I cannot understand how that is all you are getting in the file ... and that is what is shocking me.  Without the info i'm at a loss to think how to try and proceed ... I was hoping to validate the GUID which if you dd not know I can say should not change between versions ... but something is amiss and I was hoping that would give me a clue.

The complete abscence of so much of the info has thrown me!

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
And I have tested the code in ecel 2003 under XP and it loads the libraries as well as the response from the what ref is fine.

Whilst it should be a copy I have posted the exact code I used for that statement below both the AddReference_Test and whatRefs subs which worked as expected.

Chris
Sub AddReference_Test()
Dim strGUID As Variant
Dim itm As Variant
Dim ref As Variant
Dim office_GUID As Variant
Dim i As Long
' Office C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' VBA C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL    {000204EF-0000-0000-C000-000000000046}
' Excel   C:\Program Files\Microsoft Office\Office12\EXCEL.EXE    {00020813-0000-0000-C000-000000000046}
' Word    C:\Program Files\Microsoft Office\Office12\MSWORD.OLB   {00020905-0000-0000-C000-000000000046}
' Access  C:\Program Files\Microsoft Office\Office12\MSACC.OLB    {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
' VBScript_RegExp_55  C:\Windows\system32\vbscript.dll\3  {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
' stdole  C:\Windows\system32\stdole2.tlb {00020430-0000-0000-C000-000000000046}
' VBIDE   C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB {0002E157-0000-0000-C000-000000000046}
' MSForms C:\Windows\system32\FM20.DLL    {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' Scripting   C:\Windows\system32\scrrun.dll  {420B2830-E718-11CF-893D-00A0C9054228}
' MAPI    C:\Program Files\Common Files\system\MSMAPI\2057\cdo.dll   {3FA7DEA7-6438-101B-ACC1-00AA00423326}
' ADODB   C:\Program Files\Common Files\System\ado\msado15.dll    {B691E011-1797-432E-907A-4D8C69339129}
' DAO C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll   {00025E01-0000-0000-C000-000000000046}
' ACRODISTXLib    C:\Program Files\ADOBE\DISTILLR\ACRODIST.EXE    {317DA881-ECC5-11D1-B976-00600802DB86}
' Powerpoint  C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE11\MSPPT.OLB    (91493440-5A91-11CF-8700-00AA0060263B}

    On Error Resume Next
    For i = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(i)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next i
    office_GUID = Array("{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", "{00020905-0000-0000-C000-000000000046}", "{91493440-5A91-11CF-8700-00AA0060263B}")
    For Each strGUID In office_GUID
        Err.Clear
        Application.VBE.ActiveVBProject.References.AddFromGuid _
        GUID:=strGUID, major:=0, minor:=0 ' 0:0 to use latest versions installed

         'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
                Stop
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
    Next
    On Error GoTo 0
End Sub

Sub whatRefs()
Dim FSO As Object
Dim outputFile As Object
Dim outputPath As String
Dim outputFileName As String
Dim ref As Long
Dim VBP As Object

    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"
    Set FSO = CreateObject("scripting.filesystemobject")
    If VBA.Right(outputPath, 1) <> "\" Then outputPath = outputPath & "\"
    On Error Resume Next
    If Not FSO.FolderExists(outputPath) Then
        FSO.CreateFolder outputPath
    End If
    Set outputFile = FSO.createtextfile(outputPath & outputFileName, True)
        Set VBP = Application.ThisWorkbook.VBProject

    On Error Resume Next
    For ref = 1 To VBP.References.Count
        With VBP.References(ref)
            outputFile.Write "' " & .Name & " Library" & String(40 - Len(.Name), " ") & "  Major, " & .major & "  Minor, " & VBA.Right("  " & .minor, 2) & "     " & .GUID & vbCrLf
        End With
    Next
    On Error GoTo 0
    outputFile.Close

End Sub

Open in new window

0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Frankly speaking, this is all going a wee bit over me.

In layman's terms; the problem is still there. I am not able to delete the broken reference by code. I have to do it manually.

Saqib
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
As long as you have trusted access to the VBProject and put the code that fixes the referenecs into its own module, then it should be fine.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
As Rory has commented, the code should reside in a normal code module.  Using insert module from the VBE menu.

With ref to the lines in whatrefs:

    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"

Can you delete the output file on your system
Add the required references manually
run the whatrefs sub again and return the data again

Assuming that works I will modify the AddReference_Test before you upload it again and re-run it.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Code is already in a normal module (insert > module)

"Trust access to Visual Basic Project" is already checked

Contents of output file from whatrefs follow
' VBA Library                                       Major, 4  Minor,  0     {000204EF-0000-0000-C000-000000000046}
' Excel Library                                     Major, 1  Minor,  5     {00020813-0000-0000-C000-000000000046}
' stdole Library                                    Major, 2  Minor,  0     {00020430-0000-0000-C000-000000000046}
' Office Library                                    Major, 2  Minor,  3     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' MSForms Library                                   Major, 2  Minor,  0     {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' AutoCAD Library                                   Major, 1  Minor,  0     {851A4561-F4EC-4631-9B0C-E7DC407512C9}

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Okay first off my apologies ... I didn't realise your earlier printout was in a scroll window so I completely missed the reference details.

I see you are using it to try and add autocad.  Not one I have had experience with but it is supposed to work the same as any others!

For a test can you ignore that one for a moment and repeat the activity but use a reference you do not currently have, ideally word or excel and see if the code runs and functions correctly for them.

If the code does work as per my own usage then we can concentrate on why Autocad should be different.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Hi again. Sorry for being sleeping on it for long times. Actually I need to get access to others' computers to test this and this is what keeps me from testing it.

When I save the excel file in one version of excel and open it in another version the version of excel reference is always updated to the current version.

But for autocad the version is saved with the file and comes up as saved.

So I really do not know how to test the other references.

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Lets try deleting it from the install before reloading it and see if that has the required effect.

The modified script is set for Autocad and delete then add reference

Chris
Sub AddReference_Test()
Dim strGUID As Variant
Dim itm As Variant
Dim ref As Variant
Dim office_GUID As Variant
Dim I As Long
' Office C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' VBA C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL    {000204EF-0000-0000-C000-000000000046}
' Excel   C:\Program Files\Microsoft Office\Office12\EXCEL.EXE    {00020813-0000-0000-C000-000000000046}
' Word    C:\Program Files\Microsoft Office\Office12\MSWORD.OLB   {00020905-0000-0000-C000-000000000046}
' Access  C:\Program Files\Microsoft Office\Office12\MSACC.OLB    {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
' VBScript_RegExp_55  C:\Windows\system32\vbscript.dll\3  {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
' stdole  C:\Windows\system32\stdole2.tlb {00020430-0000-0000-C000-000000000046}
' VBIDE   C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB {0002E157-0000-0000-C000-000000000046}
' MSForms C:\Windows\system32\FM20.DLL    {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' Scripting   C:\Windows\system32\scrrun.dll  {420B2830-E718-11CF-893D-00A0C9054228}
' MAPI    C:\Program Files\Common Files\system\MSMAPI\2057\cdo.dll   {3FA7DEA7-6438-101B-ACC1-00AA00423326}
' ADODB   C:\Program Files\Common Files\System\ado\msado15.dll    {B691E011-1797-432E-907A-4D8C69339129}
' DAO C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll   {00025E01-0000-0000-C000-000000000046}
' ACRODISTXLib    C:\Program Files\ADOBE\DISTILLR\ACRODIST.EXE    {317DA881-ECC5-11D1-B976-00600802DB86}
' Powerpoint  C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE11\MSPPT.OLB    (91493440-5A91-11CF-8700-00AA0060263B}

    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    office_GUID = Array("{851A4561-F4EC-4631-9B0C-E7DC407512C9}")
    For Each strGUID In office_GUID
        RemoveReference CStr(strGUID)
        Err.Clear
        Application.VBE.ActiveVBProject.References.AddFromGuid _
        GUID:=strGUID, major:=0, minor:=0 ' 0:0 to use latest versions installed

         'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
                Stop
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
    Next
    On Error GoTo 0
End Sub

Sub whatRefs()
Dim FSO As Object
Dim outputFile As Object
Dim outputPath As String
Dim outputFileName As String
Dim ref As Long
Dim VBP As Object

    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"
    Set FSO = CreateObject("scripting.filesystemobject")
    If VBA.Right(outputPath, 1) <> "\" Then outputPath = outputPath & "\"
    On Error Resume Next
    If Not FSO.FolderExists(outputPath) Then
        FSO.CreateFolder outputPath
    End If
    Set outputFile = FSO.createtextfile(outputPath & outputFileName, True)
        Set VBP = Application.ThisWorkbook.VBProject

    On Error Resume Next
    For ref = 1 To VBP.References.Count
        With VBP.References(ref)
            outputFile.Write "' " & .Name & " Library" & String(40 - Len(.Name), " ") & "  Major, " & .major & "  Minor, " & VBA.Right("  " & .minor, 2) & "     " & .GUID & vbCrLf
        End With
    Next
    On Error GoTo 0
    outputFile.Close

End Sub

Sub RemoveReference(strGUID As String)
Dim I As Integer
Dim ref As Object
    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.GUID = strGUID Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    On Error GoTo 0
End Sub

Open in new window

0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
This is just about what I have been trying to do - delete the reference but to not avail. I am not sure about the exact syntax. Will give this a try next week. Till then, thanks.

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
The code supplied will delete it from the application .. have you tried to see if it helps?
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
This suggestion is included in the link I have provided in the question and I have already tried it out.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
I tried it on excel 2007 also but the broken reference is not deleted.

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Do I understand correctly that:

1. Other MS applications refresh the relevanr references with no proble.
2. Autocad reference is displayed BUT always reflects the original PC GUID.

Assuming 2 is correct and in regard to trying to find a solution.  WHat happens if you open two documents from different versions ... what is diplayed for the GUID in each case both individually and seperately?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
1 and 2 are correct.

I am not sure I fully understand your question.

If I open a file saved with the guid for Acad 2010 on a computer running 2008 the references show autocad 2010. I do not know what you mean by individually or separately.

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
:o) rephrasing:


Open 2010 document on 2008 install --> Autocad 2010 reference
Open 2008 document on 2008 install --> Which GUID?

Open both ... when switching between documents in the project does that affect anything?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Chris, I believe you are online.

Sorry for delaying this. I am alone at the office so I can play around with other computers on this issue.

I have saved two files. One from 2011 and the other from 2008

What next.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
If the files you refer to are the result of running Sub whatRefs then Can you upload the contents of both files?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
No these are my excel files.

I cannot run the whatrefs unless I change the ref to 2008. So there is no point in doing that exercise.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Not sure I follow.  The idea was to take a 2008 Autocad install and a 2011 autocad install and add the whatref sub to each.  When run this will return the (globally used) GUID's for the installed programs, i.e I want to see any differences).

It is this data I am looking to review and this ought to run whatever the situation ... or am I missing something?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Ok here they are.

To be able to run the 2011 file I had to remove the string() function and put in some spaces. But then it would not output the Autocad ref
GUID2008.txt
GUID2011.txt
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I will admit to be being confused ... but in a way that is good a small chance I can learn something, (without forgetting something else in the process).

When you talk about 2011 you are referring to a PC with an install of autocad 2011 and on that install the GUID does not work?

Try it again on 2011 install but using vba.string which should bypass the reference error.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
No, I am making all attempts from an installation of Autocad 2008. Other people have 2011 and save it thus.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Given the time that has passed can you refresh me then ...

You have more than one install of autocad 2008 and on some of them the reference for autocad shows up as missing and cannot be added?  Assuming so then on a working machine and also a broken one what if anything is shown for the file object location?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Sorry am back home now.

We have some computers running Autocad 2008 and some 2011.
Only one user at a time can use the file.
When a user on 2011 uses the excel file he saves the file with references to 2011. Next time if a user with 2008 uses the file he has to change the reference to 2008. Again, subsequently, when someone with 2011 uses the file he has to return the references to 2011.

I am trying to automate this manual re-referencing for which I first have to delete the existing broken reference and this is where I am failing.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Pretty much what I thought ... so on one of the pc's where the user has 2011, can you load and run the whatrefs sub.  From my perspective I appreciate I am accusing you of being in error and for that I apologise but what I am now looking for is a note of the GUID that exists on a 2011 machine that is not throwing up the error.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Thanks, Teylyn for waking me up on this. Actually I need to get hold of one of my colleagues' computer to try this suggestion which is why this is being delayed frequently. I shall try to wind this up soon.

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I'll be glad to see an update, (I hope) so that I can try to understand the conflict between 2008/2011.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Sorry Chris for holding you up. Here is the output from the machine with Autocad 2011

' VBA Library                                       Major, 4  Minor,  1     {000204EF-0000-0000-C000-000000000046}
' Excel Library                                     Major, 1  Minor,  7     {00020813-0000-0000-C000-000000000046}
' stdole Library                                    Major, 2  Minor,  0     {00020430-0000-0000-C000-000000000046}
' Office Library                                    Major, 2  Minor,  5     {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
' MSForms Library                                   Major, 2  Minor,  0     {0D452EE1-E08F-101A-852E-02608C4D0BB4}
' AutoCAD Library                                   Major, 1  Minor,  0     {D32C213D-6096-40EF-A216-89A3A6FB82F7}

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Okay then ... some hope for a solution since they are different.  Now we need to read the version of the application in order to try and set it:

2008 {851A4561-F4EC-4631-9B0C-E7DC407512C9}
2011 {D32C213D-6096-40EF-A216-89A3A6FB82F7}

In the debug window of Autocad, ctrl + G to display type:

?application.version

Try this with both 2008 and 2011 installs and if we get a valid response in each case I think we will be good to go with a little bit of VBA.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
?application.version gives 11.0 but this application is Excel.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
My computer

? application.version gives                                                   11.0

? GetObject(, "AutoCAD.Application").version           17.1s (LMS Tech)


Other computer

? application.version gives                                                   14.0

? GetObject(, "AutoCAD.Application").version           18.1s (LMS Tech)
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
WoW, I forgot for a moment you are one of 'us'.  A full response if ever I saw one.

How do you want to 'run' the script ... as a vbs on the users machine or (sorry no access to ACAD) when opening the file ... assuming you can run a script when opening the file in autoCAD.

I imagine you can see how I am proceeding here, but of course I am expecting to implement it anyway.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Assuming it is something run in the application then see setVer.  I have left it such that different variants can be added with ease.

If it works then great but my understanding is that the GUID is supposed to be common for an application so it seems naughty naughty that we have seen otherwise, (or stupid stupid if i'm wrong of course).

Still hopefully ...

Chris
Sub setVer()
Const ver2008 = "{851A4561-F4EC-4631-9B0C-E7DC407512C9}"
Const ver2011 = "{D32C213D-6096-40EF-A216-89A3A6FB82F7}"

    If Application.Version = 11# Then
        RemoveReference ver2011
        AddReference ver2008
    ElseIf Application.Version = 14# Then
        RemoveReference ver2008
        AddReference ver2011
    Else
        MsgBox "Unsupported Version"
    End If
End Sub

Sub RemoveReference(strGUID As String)
Dim I As Integer
Dim ref As Object
    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.GUID = strGUID Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    On Error GoTo 0
End Sub

Sub AddReference(strGUID As String)
Dim itm As Variant
Dim ref As Variant
Dim I As Long

    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    
    Application.VBE.ActiveVBProject.References.AddFromGuid _
    GUID:=strGUID, major:=0, minor:=0 ' 0:0 to use latest versions installed

     'If an error was encountered, inform the user
    Select Case Err.Number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
            Stop
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0

End Sub

Open in new window

0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Chris, the line

            Application.VBE.ActiveVBProject.References.Remove ref

works well for a healthy ref but does not work for a broken ref. If I comment out the "on error.." statement it gives an error "Object library not registered"

I would be running this sub from excel's VBA

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Okay, added the block to delete broken references so if that helps?

Chris
Sub setVer()
Dim intRefCount As Integer
Dim ref As Object
Const ver2008 = "{851A4561-F4EC-4631-9B0C-E7DC407512C9}"
Const ver2011 = "{D32C213D-6096-40EF-A216-89A3A6FB82F7}"

    For intRefCount = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(intRefCount)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next
    If Application.Version = 11# Then
        RemoveReference ver2011
        AddReference ver2008
    ElseIf Application.Version = 14# Then
        RemoveReference ver2008
        AddReference ver2011
    Else
        MsgBox "Unsupported Version"
    End If
End Sub

Sub RemoveReference(strGUID As String)
Dim I As Integer
Dim ref As Object
    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.GUID = strGUID Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    On Error GoTo 0
End Sub

Sub AddReference(strGUID As String)
Dim itm As Variant
Dim ref As Variant
Dim I As Long

    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    
    Application.VBE.ActiveVBProject.References.AddFromGuid _
    GUID:=strGUID, major:=0, minor:=0 ' 0:0 to use latest versions installed

     'If an error was encountered, inform the user
    Select Case Err.number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
            Stop
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0

End Sub

Open in new window

0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Chris, this line

            Application.VBE.ActiveVBProject.References.Remove ref

is what I already had from the link in the question. The problem is that it does not work. It gives the error "Object library not registered" for broken refs but works well for good refs.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Since we are removing broken refs with the change ... hopefully we can delete the delete though it shouldn't be sensitive to that ... stick with your experience and give this a try.

Chris
Sub setVer()
Dim intRefCount As Integer
Dim ref As Object
Const ver2008 = "{851A4561-F4EC-4631-9B0C-E7DC407512C9}"
Const ver2011 = "{D32C213D-6096-40EF-A216-89A3A6FB82F7}"

    For intRefCount = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(intRefCount)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next
    If Application.Version = 11# Then
'        RemoveReference ver2011
        AddReference ver2008
    ElseIf Application.Version = 14# Then
'        RemoveReference ver2008
        AddReference ver2011
    Else
        MsgBox "Unsupported Version"
    End If
End Sub

Sub RemoveReference(strGUID As String)
Dim I As Integer
Dim ref As Object
    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.GUID = strGUID Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    On Error GoTo 0
End Sub

Sub AddReference(strGUID As String)
Dim itm As Variant
Dim ref As Variant
Dim I As Long

    On Error Resume Next
    For I = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(I)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next I
    
    Application.VBE.ActiveVBProject.References.AddFromGuid _
    GUID:=strGUID, major:=0, minor:=0 ' 0:0 to use latest versions installed

     'If an error was encountered, inform the user
    Select Case Err.number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
            Stop
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0

End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Note I am assuming it is the delete in the deleteref sub that I am assuming to be at fault NOT the new one added to handle broken ref's

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Yes, Chris, this is where the problem is. Deleting the broken sub.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
Looking into the difference, 'Broken' refs have to be deleted manually, 'missing'  on the other hand can be handled.

Prompts the reverse thought ... I hate to give up.  How about if we try removing the 'working'  reference before closing the file ... not sure what it'll do but it has the potential to fix the issue at source rather than needing to apply the delete manually?

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
...try removing the 'working'  reference before closing the file...

Sounds promising as a workaround. I hope I get a chance to test it tomorrow at office.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Hi, Chris,

Today I got a chance to try this out and yes it works.

But the other problem I have now run into is the binding business which is driving me crazy. I really do not understand this stuff. I have found ways to get around this but the normal objects like thisworkbook and activeworkbook are not working anymore following a getobject.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
In precis:

Early BInding, the script knows all about the object type ... therefore intellisense works where supported.  In order to work a file of data has to be available, this is a library file added to the interface via the tools references of the VBE and must reside on all pc's where the script is used.

Late Binding, the variable is assigned as an object, VBA does not know what it is so intellisense is unavailable.  No library file is required since the capabilities of the variable are evaluated at run time.  It will therefore work the same everywhere.

thisworkbook and activeworkbook will still work but the types need to be defined as object in your code so that the library files do not matter.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
Yes, I understand the logic. But when it comes down to putting it to code I get confused. Anyway I shall try it again some other time if I remember to.

I think the question, strictly speaking, is solved and I would not like to keep this going for ever so I shall close it at this point.

Thanks a million for bearing with me through all those times I went into hibernation.

Saqib
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
If you do encounter problems with the context another time then of course someone is always here ... though the someone may be yourself!

Realistically though when you find a specific example of being 'confused' post it up without trying to work your way through it and emphasise you want to understand the reasons for the problem rather than simply getting it fixed ... and that will provide the foundation for understanding.

Chris
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
Comment Utility
:)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now