Office 2000 - Office 2003

[EA]

I am trying to write code that works on both in Excel 2000 and Excel 2003.
My problem is with the references.

In 2000 the reference to the Outlook Library is:

C:\Program Files\Microsoft Office\Office10\msoutl.olb

In 2003 it is:

C:\Program Files\Microsoft Office\Office11\msoutl.olb

I would like to enter some code in the Open event of the workbook that
checks the environment and adds the correct reference without producing an
error.

Any help or references would be appreciated.

 

[Peter Rooney]

Hi, EA,

Assuming that your installations of Excel went to the default locations,
could you use Application.Version, to determine which version you were
running and run suitable code as appropriate?

Cheers

Pete

 

[Peter T]

In 2000 the reference to the Outlook Library is:

C:\Program Files\Microsoft Office\Office10\msoutl.olb

That would suggest the system has Excel 2000 and Outlook from Office 2002
(it might also have Outlook 2000)

If you save your project on a machine that only has Outlook 2000 it should
work on all versions from 2000 on. Also ensure your code only includes
methods supported by the earliest version, or cater for differences in later
versions (if any).

Regards,
Peter T


If you build and save your project on a system that only has

 

[EA]

The problem is when the file is run on a machine that has Office 2003 on it,
the reference is updated to C:\Program Files\Microsoft
Office\Office11\msoutl.olb, then when the same file is attempted to be run
on a PC that only has Office 2000 on it, the reference is Missing - and so
causes a problem.

What is the recommended way around this i.e. being able to use a file on
both systems at the same time?

 

[Peter T]

So you are saying the same file gets passed back and forth between the
respective PC's being saved each time on each.

Could you convert to Late Binding. No reference required, less efficient but
chances are the difference wouldn't be noticeable.

Regards,
Peter T

 

[EA]

Sub IMPORT()

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook

Dim xlAppPv As Excel.Application
Dim xlWbkPv As Excel.Workbook

Calculate
Application.ScreenUpdating = False

Path = Range("Trading_Path").Value <----------Highlighted-------
ext = Range("Trading_Name").Value


I think I understand the difference between early and late binding.

If I put a 'Dim Path as string' at the start of the procedure and compile I
do not get the error.

However I am unclear in my mind why the code above works in either
enviroment. From which library does the String type functionality come?
And so how does the code work?

Initially thre code was written in Excel 2000 and worked. Then it was run
on an Excel 2003 machine and worked (and saved) then when the saved file was
run on a Excel 2000 PC an error message appears which says "Compile error,
cannot find project or library.

And so we are back to the missing

C:\Program Files\Microsoft Office\Office11\msoutl.olb

file.

Surely the type definitions do not come from this file - so why is it so
important.

I have advised that future cosing should be done using Option Explicit, to
force binding, but I am struggling to understand why the code worked in the
first place.

Can anyone offer any clarifictaion (espeically Peter T - many thanks for
assisting on nthis so far)

 

[Peter T]

Initially thre code was written in Excel 2000 and worked. Then it was run

on an Excel 2003 machine and worked (and saved) then when the saved file was
run on a Excel 2000 PC an error message appears which says "Compile error,
cannot find project or library.

That would be expected

C:\Program Files\Microsoft Office\Office11\msoutl.olb

Surely the type definitions do not come from this file - so why is it so
important.

Not directly but indirectly yes. This file will have various internal
references to the vba "library", which has new stuff. When these references
can't get resolved, because they don't exist in earlier versions, things go
wrong.

In other words all old the ref's will exist when you move to a new version,
but not the other way round.

When you have ANY type of missing ref, Excel vba even fails to find its own
things without a nudge. In particular String and DateTime functions.

As a temporary fix, say until you programmatically change or set the correct
ref, you can get out of trouble by changing say

Dim s as String
s = Left("abc",1)
by fully qualifying all the way back to vba
s = VBA.Strings.Left("abc"1)

But you have to be meticulous with everything (F2 Object browser is very
useful finding the right vba libraries). Also don't refer to anything with a
missing ref until it's been resolved. That means putting such stuff in a
module that's not been compiled and won't be at runtime until everything's
fixed.

Don't suppose you wanted to know all that but you did ask for an
explanation! So for your purposes change to Late Binding and avoid the
problems.

Start by removing the Outlook reference and change say

Dim olApp as Outlook.application
to
Dim olApp as Object

No doubt more changes to make, eg change the olConstants to their values.

Head modules Option Explicit, declare all your variables and Debug >
compile.

Loads more in this ng re Early vs Late Binding

Regards,
Peter T