Sleeper: 'Unchecked' references

[stanl]

For a given workbook I am able to obtain information about project references and whether the link is good:

For Each proj In ThisWorkbook.VBE.VBProject.VBProjects
....
....
For Each ref in proj.References
....
....
Next ref
Next proj

but this only iterates the checked boxes. Is there a way to obtain the name and path of unchecked items?

Stan

[Bob Phillips]

I don't think this is possible.

.

[stanl]

I don't think this is possible.

Hmmm. which sort of begs the question as to where Excel gets that information to display.

Thank you.

Stan

[Bob Phillips]

Hmmm. which sort of begs the question as to where Excel gets that information to display.

Excel has a ton of information that is not exposed to the object model.

[stanl]

Excel has a ton of information that is not exposed to the object model.

understood... but at sometime something was done either through WMI or the Registry to obtain a list of registered typelibs/dlls/ocxs on the system to populate the initial list of available references with Name, path, GUID with a property for isBroken.

I guess to put this into the larger context of my question. If one creates a workbook with a reference to Object x - checked, and that workbook is ported to another PC where Object x exists, albeit in a different path, or maybe has never been checked on any workbook in that system - what is the status of the workbook in the new location? Or another way to put it, are the references part of the workbook or the application?

Stan

[TonyJollans]

References are part of the Workbook. When the workbook is opened Excel/VBA tries to resolve them. Sometimes it manages different versions and/or paths correctly, sometimes it doesn't. If it can't resolve a reference it sets it to BROKEN. You can check for broken references - if you (your code) know the one you're dealing with you may be able to provide some intelligence that Excel doesn't have but, unless you can do that, I don't see how having access to the list would help you and, if you can do that you can probably manage without the list.

Out of personal interest I would like to know a bit more about how the list is populated but I don't think it likely to be useful in VBA code.

[stanl]

but I don't think it likely to be useful in VBA code.Today 02:10 PM

to quote a friend (on another BBS)

I'd create reports in VBA, dialogs, etc and then ship it off to another employee...then the programs often didn't work! Even on PCs with the exact image as mine (files and dlls in the same named folders, etc).

First order of business: open excel, go to References, uncheck all being used and then recheck them. Resave the XLS file. Now it works.

I rarely use excel anymore, has that "feature" been fixed ?

some of us who work in situations such as these would probably consider it useful... if not in strictly in VBA code

[johnske]

Echoing what Tony said, I don't see the point in iterating all the UNchecked references Stan. You can find all the references that are being used and check (set) them programmatically with a workbook open event - this may be more useful...

Also, I've never struck this before (Office 2000) - would be interested to see if anyone else has (earlier versions maybe?):

I'd create reports in VBA, dialogs, etc and then ship it off to another employee...then the programs often didn't work! Even on PCs with the exact image as mine (files and dlls in the same named folders, etc).

First order of business: open excel, go to References, uncheck all being used and then recheck them. Resave the XLS file. Now it works.

I rarely use excel anymore, has that "feature" been fixed ?

[stanl]

I want to thank eveyone for their comments. I meant to be neither annoying or insistent. I adopted the brute force method. Created a testref.xls and manually marked all references that could be resolved. Then wrote code to iterate the name,description,major,minor,GUID,path,broken and builtin properties to an XML recordset. This can then be used to verify (by existence of either the file/path or the GUID) references on another PC prior to even invoking Excel or other applications. the testref file can then be used as a repository for adding additional references (i.e. like Tidy, xStandard, VSFlex, ChilKat...) and the xml easily updated.

If anyone is interested in the XML I can post it.

Stan

[johnske]

Hi Stan,

Although I personally consider it an overkill, what your friend said about uncheching and then checking all the references can be done programmatically as follows

Option Explicit
 
Private Sub Workbook_Open()
      Dim RestoreRef As String, Reference As Object, N As Long
      With ThisWorkbook.VBProject.References
            For N = 1 To .Count
                  Set Reference = .Item(N)
                  RestoreRef = Reference.GUID
                  On Error Resume Next    '< cant remove default refs
                  .Remove Reference
                  .AddFromGuid _
                              GUID:=RestoreRef, Major:=1, Minor:=0
            Next N
      End With
      Set Reference = Nothing
End Sub

HTH,
John