wileedingo
asked on
Setting references problem
Hello,
I have a database which includes references to the Word and Excel object libraries. It has been developed in Access2000 format using Office xp (ver 10) and there are no problems running it as-is in Office 03 (ver 11). The problem has come up where someone is running the database in Office2000 (Access has been upgraded to 2002).
I have modified a routine from the MS web site which removes broken references. After that, it figures out the verson of office and resets the references. Code is further below...
When running it on the Office2000 machine I get the following error messages. I'm thinking the version numbers are all wrong...
"Your [database name] database or project contains a missing or broken reference to the file 'msword.olb' Version 8.2"
and
"Your [database name] database or project contains a missing or broken reference to the file 'excel.exe' Version 1.6"
Here's the code...
Dim loRef As Access.Reference
Dim intCount As Integer
Dim intX As Integer
Dim blnBroke As Boolean
On Error Resume Next
'Count the number of references in the database
intCount = Access.References.count
'Loop through each reference in the database
'and remove broken references
For intX = intCount To 1 Step -1
Set loRef = Access.References(intX)
With loRef
blnBroke = .IsBroken
If blnBroke = True Or Err <> 0 Then
With Access.References
.Remove loRef
End With
End If
End With
Next
Set loRef = Nothing
' now set the references
If (Dir("c:\Program Files\Microsoft Office\Office11\msword.olb
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office11\msword.olb
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office10\msword.olb
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office10\msword.olb
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office\msword9.olb"
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office\msword9.olb"
End With
End If
If (Dir("c:\Program Files\Microsoft Office\Office11\excel.exe"
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office11\excel.exe"
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office10\excel.exe"
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office10\excel.exe"
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office\excel9.olb")
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office\excel9.olb"
End With
End If
Any ideas on how to fix or improve this?
wileedingo
I have a database which includes references to the Word and Excel object libraries. It has been developed in Access2000 format using Office xp (ver 10) and there are no problems running it as-is in Office 03 (ver 11). The problem has come up where someone is running the database in Office2000 (Access has been upgraded to 2002).
I have modified a routine from the MS web site which removes broken references. After that, it figures out the verson of office and resets the references. Code is further below...
When running it on the Office2000 machine I get the following error messages. I'm thinking the version numbers are all wrong...
"Your [database name] database or project contains a missing or broken reference to the file 'msword.olb' Version 8.2"
and
"Your [database name] database or project contains a missing or broken reference to the file 'excel.exe' Version 1.6"
Here's the code...
Dim loRef As Access.Reference
Dim intCount As Integer
Dim intX As Integer
Dim blnBroke As Boolean
On Error Resume Next
'Count the number of references in the database
intCount = Access.References.count
'Loop through each reference in the database
'and remove broken references
For intX = intCount To 1 Step -1
Set loRef = Access.References(intX)
With loRef
blnBroke = .IsBroken
If blnBroke = True Or Err <> 0 Then
With Access.References
.Remove loRef
End With
End If
End With
Next
Set loRef = Nothing
' now set the references
If (Dir("c:\Program Files\Microsoft Office\Office11\msword.olb
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office11\msword.olb
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office10\msword.olb
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office10\msword.olb
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office\msword9.olb"
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office\msword9.olb"
End With
End If
If (Dir("c:\Program Files\Microsoft Office\Office11\excel.exe"
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office11\excel.exe"
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office10\excel.exe"
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office10\excel.exe"
End With
ElseIf (Dir("c:\Program Files\Microsoft Office\Office\excel9.olb")
With Access.References
.AddFromFile "C:\Program Files\Microsoft Office\Office\excel9.olb"
End With
End If
Any ideas on how to fix or improve this?
wileedingo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Basically where every you have
Dim oxl as Excel.APplication
Dim oWord as Word.Application
Set oXl = New excel.Application
Set oword = new word.aaplication
replace with
Dim oxl as Object
Dim oWord as Object
Set oxl = CreateObject("Excel.Applic
Set oWord = CreateObject("Word.Applica
Dave
Dim oxl as Excel.APplication
Dim oWord as Word.Application
Set oXl = New excel.Application
Set oword = new word.aaplication
replace with
Dim oxl as Object
Dim oWord as Object
Set oxl = CreateObject("Excel.Applic
Set oWord = CreateObject("Word.Applica
Dave
ASKER
Sooo much easier. Most excellent help. Thanks muchly.
wileedingo
wileedingo
To easy mate.
Good luck with your app!
Dave
Good luck with your app!
Dave
ASKER
I ran my code at startup. Would you instead just plug this in where you need to use Excel or Word? Basically, I have two procedures, one which opens a Word doc and the other an Excel file.