Reference Library 1

[Firery]

Hi, I have a code in my "Before_Close" Sub, which stops the user closing my Excel file using the X in the top right corner.
To exit I have an exit button which sets range "A50" to TRUE,then saves and quits. This bypasses the
"If CloseMode".
My Workbook_Open sets "A50" to FALSE


If Range("A50") = FALSE Then
If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End If

My problem is, in my Office XP Pro, it works great but when I take it to a system running Office 2000, the code fails. The reference library says that MS Word 10.0 Object library is missing. I then select the MS Word 9.0 Object library and everything is working again. Fine until I take it back to my system where it resets the reference to MS Word 10.0 again.
I move my program from home to work frequently.

How do I get around this problem?

Thanks for any assistance!

 

[fumei]

You need some way of detecting what reference to use. Have you Open event check the version, then reset the references programatically to match.

Sometimes getting the version correctly can be odd. So what I do is I have a simple ASCII file that is parsed from the Open event. It has version info, as well as the full path of references that are troublesome. These can be picked up as string variables and used when programatically adjusting references.

Look up the Reference object for how to change the references.

Gerry

 

[RoyVidar]

I'd investigate late binding.

Roy-Vidar

 

[PHV]

Or use late binding (instead of early binding) when you automate Word from within Excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lupins46]

You need to read about 'Late Binding' in Help.
What you are doing at the moment is referred to as 'Early Binding' which in essence means that you are telling your program which applications (and their version) you are going to use in your code. You do this by setting the Reference to the object library. The single biggest adavantage of this when programming is that you get Intellisense prompts.

With Late Binding you do not set references; you define objects and in code create your Word Application object.
This picks up whatever version of Word the user has installed and is the technique generally used when implementing in a mixed enivironment. The downside for program development is that you do not get any Intellisense prompts for code related to that application.
Also, you cannot use any intrinsic constants (constants that begin wd.... )for that application as your programmming environment does not know what they mean - so you have to set these up yourself with their values.

If this issue only affects you then you might decide it is better to put up with it to avoid the loss of Intellisense.

 

[combo]

After referencing Microsoft Visual Basic For Applications Extensibility 5.3 (VBIDE) and, in xp, allowing access to vba project (security settings), this piece of code can automate referencing (to remove in final project, locked and saved under office 2000):

Private Sub Workbook_Open()
With ThisWorkbook.VBProject.References
If .Item("Word").IsBroken Then
.Remove Reference:=.Item("Word")
Select Case Application.Version
Case "10.0"
.AddFromFile Application.Path & "\msword.olb"
Case "9.0"
.AddFromFile Application.Path & "\msword9.olb"
End Select
End If
End With
End Sub

combo

 

[Firery]

Thank to All for you input, but I got around my problem very simply,
In my Sub - "BeforeClose(Cancel As Boolean)"
I placed;
If Range("a50") = False Then
Cancel = True
End If
(A50 is in an out of the way section of my program)
My EXIT button sets A50 to True and then Quits the Application.
My Autpen resets A50 to False, so that the only way to quit is to press the EXIT button.
It works nicely in all Excel versions.

Thanks again

 

[lupins46]

Hi Firery

It's great that you have a working program; but your solution has nothing to do with the question you asked.
If you continue to move between different office environments you will continue to have the problem you described originally. The solution to THAT problem is late binding , as everyone has indicated.