Saqib Husain
asked on
Deleting references
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
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
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
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
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
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
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
Chris
ASKER
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.
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.
What OS / Win ver?
Chris
Chris
ASKER
Windows Xp
Office 2003
Autocad 2008
other machines have
Windows 7
office 2003 or 2007
Autocad 2008 or 2011
Office 2003
Autocad 2008
other machines have
Windows 7
office 2003 or 2007
Autocad 2008 or 2011
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
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
ASKER
I tried adding the required reference by the simple routine
Sub SetRef()
Dim strGUID As String
strGUID = "{851A4561-F4EC-4631-9B0C- E7DC407512 C9}"
ThisWorkbook.VBProject.Ref erences.Ad dFromGuid GUID:=strGUID, major:=1, minor:=0
End Sub
but it gave
Name conflicts with existing module, project, or object library
Sub SetRef()
Dim strGUID As String
strGUID = "{851A4561-F4EC-4631-9B0C-
ThisWorkbook.VBProject.Ref
End Sub
but it gave
Name conflicts with existing module, project, or object library
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
If the reference does not add manually ten find a machine where itr does exist and run the whatrefs sub
Chris
ASKER
' 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}
Now that's a shock ... I was expecting something like:
' VBA Library Major, 4 Minor, 1 {000204EF-0000-0000-C000-0 0000000004 6}
' Excel Library Major, 1 Minor, 7 {00020813-0000-0000-C000-0 0000000004 6}
' MSForms Library Major, 2 Minor, 0 {0D452EE1-E08F-101A-852E-0 2608C4D0BB 4}
' stdole Library Major, 1 Minor, 0 {00020430-0000-0000-C000-0 0000000004 6}
' Office Library Major, 2 Minor, 5 {2DF8D04C-5BFA-101B-BDE5-0 0AA0044DE5 2}
Which app are you running it in?
' VBA Library Major, 4 Minor, 1 {000204EF-0000-0000-C000-0
' Excel Library Major, 1 Minor, 7 {00020813-0000-0000-C000-0
' MSForms Library Major, 2 Minor, 0 {0D452EE1-E08F-101A-852E-0
' stdole Library Major, 1 Minor, 0 {00020430-0000-0000-C000-0
' Office Library Major, 2 Minor, 5 {2DF8D04C-5BFA-101B-BDE5-0
Which app are you running it in?
ASKER
Excel 2003
I do not see what is it that seems shocking.
I do not see what is it that seems shocking.
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
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
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
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
ASKER
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
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
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.
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
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
ASKER
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
"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}
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
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
ASKER
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
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
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
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
ASKER
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
Saqib
The code supplied will delete it from the application .. have you tried to see if it helps?
ASKER
This suggestion is included in the link I have provided in the question and I have already tried it out.
ASKER
I tried it on excel 2007 also but the broken reference is not deleted.
Saqib
Saqib
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
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
ASKER
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
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
: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
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
ASKER
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.
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.
If the files you refer to are the result of running Sub whatRefs then Can you upload the contents of both files?
Chris
Chris
ASKER
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.
I cannot run the whatrefs unless I change the ref to 2008. So there is no point in doing that exercise.
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
It is this data I am looking to review and this ought to run whatever the situation ... or am I missing something?
Chris
ASKER
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
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
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
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
ASKER
No, I am making all attempts from an installation of Autocad 2008. Other people have 2011 and save it thus.
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
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
ASKER
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.
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.
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
Chris
ASKER
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
Saqib
I'll be glad to see an update, (I hope) so that I can try to understand the conflict between 2008/2011.
Chris
Chris
ASKER
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}
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-E 7DC407512C 9}
2011 {D32C213D-6096-40EF-A216-8 9A3A6FB82F 7}
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
2008 {851A4561-F4EC-4631-9B0C-E
2011 {D32C213D-6096-40EF-A216-8
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
ASKER
?application.version gives 11.0 but this application is Excel.
ASKER
My computer
? application.version gives 11.0
? GetObject(, "AutoCAD.Application").ver sion 17.1s (LMS Tech)
Other computer
? application.version gives 14.0
? GetObject(, "AutoCAD.Application").ver sion 18.1s (LMS Tech)
? application.version gives 11.0
? GetObject(, "AutoCAD.Application").ver
Other computer
? application.version gives 14.0
? GetObject(, "AutoCAD.Application").ver
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
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
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
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
ASKER
Chris, the line
Application.VBE.ActiveVBPr oject.Refe rences.Rem ove 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
Application.VBE.ActiveVBPr
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
Okay, added the block to delete broken references so if that helps?
Chris
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
ASKER
Chris, this line
Application.VBE.ActiveVBPr oject.Refe rences.Rem ove 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.
Application.VBE.ActiveVBPr
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.
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
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
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
Chris
ASKER
Yes, Chris, this is where the problem is. Deleting the broken sub.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
...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.
Sounds promising as a workaround. I hope I get a chance to test it tomorrow at office.
ASKER
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.
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.
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
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
ASKER
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
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
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
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
ASKER
:)
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
Open in new window