boo boo

[john m]

Hello,

A spreadsheet that we use on every job used to have a reference to a
component that wasn't even used by the VBA program.

Now when people are changing over to office 2003 the spreadsheet throws
an error "cant find referenced component"

Of course all that is required is to uncheck the reference but most
people don't know this

Any way to batch process all these files to see if they have the
reference and if so get rid of it?

Thanks for any suggestions

JM

 

[Chip Pearson]

John,

Try some code like the following. You'll need to set a reference
to the Microsoft Visual Basic For Applications Extensibility
Library

Sub AAA()

Dim WB As Workbook
Dim FName As String
Dim DirName As String
Dim Ref As VBIDE.Reference
DirName = "H:\Test" '<< CHANGE
ChDrive DirName
ChDir DirName
FName = Dir(DirName & "\*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
For Each Ref In WB.VBProject.References
If Ref.IsBroken Then
WB.VBProject.References.Remove Ref
End If
Next Ref
WB.Close savechanges:=True
FName = Dir()
Loop

End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[john m]

awesome thank you sir

John,

Try some code like the following. You'll need to set a reference
to the Microsoft Visual Basic For Applications Extensibility
Library

Sub AAA()

Dim WB As Workbook
Dim FName As String
Dim DirName As String
Dim Ref As VBIDE.Reference
DirName = "H:\Test" '<< CHANGE
ChDrive DirName
ChDir DirName
FName = Dir(DirName & "\*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
For Each Ref In WB.VBProject.References
If Ref.IsBroken Then
WB.VBProject.References.Remove Ref
End If
Next Ref
WB.Close savechanges:=True
FName = Dir()
Loop

End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com