Office 2000 - Office 2003
 

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.

Office 2000 - Office 2003
 

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



"EA" wrote:

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.

Office 2000 - Office 2003
 

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)

Office 2000 - Office 2003
 

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


"EA" wrote in message
...
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)