Relying on basic Excel functions in Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dannerino - 16 Feb 2006 20:10 GMT

I have a multiuser Access 97 database. A references was set to Excel 8.0 to
make certain functions available (left, mid, trim, etc). I am not truly
automating Excel, simply using these functions.

As the company undergoes upgrades, users are moving up to Office 2002 or
2003. They have been allowed to keep Access 97 until the database is
approved for redesign and upgrade. The newer versions of Excel, however, are
causing problems with broken references.

If I uncheck a reference to Excel 10 or 11 object library and set it to
Excel 8, the application breaks for users with the newer versions of Excel.
If I set it back to 10 or 11, it breaks for users still with Excel 8. I
can't reference both libraries as their names are the same and it causes a
conflict.

I know about late binding, somewhat. Is this my only choice? And if so, do
I need to implement new code with late binding in every instance that I use
its functions (which is practically everywhere). Or is there a global method
I can include in my code that runs when users open the database?

Reply to this Message

George Nicholson - 16 Feb 2006 22:56 GMT

Well, first off I'd check to see whether you even need the Excel reference
at all. Some of the functions you specify (Left & mid) are VB functions and
they aren't exposed via ExcelApp.WorksheetFunction, so their usage in your
Access project has *nothing* to do with the presence of an Excel reference.
Trim is both an Excel function and a VB function that behave much the same
except Excel's Trim will reduce multiple spaces between words to a single
space.

If you uncheck the Excel reference does your code still compile?

Do you use WorksheetFunction.(NameOfExcelFunction) in your VBA code at all?
If you don't, then you aren't using the reference for functions since that's
the only way to get to them AFAIK. If you are using WorksheetFunction, what
are you using it for? There can certainly be legitimate reasons to use
WorksheetFunction, but there might be a convenient VB substitute if you tell
us what you need. (i.e., write a function to simulate Excel's "internal
Trim" capability so that you don't need the Excel reference.)

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

>I have a multiuser Access 97 database. A references was set to Excel 8.0
>to
[quoted text clipped - 21 lines]
> method
> I can include in my code that runs when users open the database?

Reply to this Message

dannerino - 17 Feb 2006 19:21 GMT

Thanks for the feedback.

I use a splash screen as the database loads, which contains code using the
left function. This screen halts on a compile error when the Excel reference
is broken. If I CTRL-BREAK at this point, the editor highlights the first
instance of the left function as the location of the error.

As I mentioned...

> > If I uncheck a reference to Excel 10 or 11 object library and set it to
> > Excel 8, the application breaks for users with the newer versions of
> > Excel.

Specifically, it breaks as it will not compile and therefore, will display a
compile error when users launch.

Also, as I mentioned...

> > I am not truly
> > automating Excel, simply using these functions.

Specifically, I do not reference the Excel application object or any of its
descendants in my code.

If these basic functions are actually VB and not Excel, then I'm
puzzled--why can't I compile without the Excel reference? I looked at my VBA
reference, and notice that my PC references vba332.dll, but there are 6 other
VBA libraries available (VBAEN32.OLB, MSVBVM60.DLL, VBAEND32.OLB, VBE6.DLL,
VEN2232.OLB and MSVBVM50.DLL).

I'd be more than happy to eliminate the Excel reference, if I can. Further
ideas?

> Well, first off I'd check to see whether you even need the Excel reference
> at all. Some of the functions you specify (Left & mid) are VB functions and
[quoted text clipped - 39 lines]
> > method
> > I can include in my code that runs when users open the database?

Reply to this Message

dannerino - 17 Feb 2006 21:17 GMT

Reply 2:
I can't tell you how many times I've attempted to remove the Excel reference
and recompile. It has never worked. That's why I thought my left, mid and
related functions were coming from Excel (although it never made sense why).
Well, it worked. Thanks for the input.

> Well, first off I'd check to see whether you even need the Excel reference
> at all. Some of the functions you specify (Left & mid) are VB functions and
[quoted text clipped - 40 lines]
> > method
> > I can include in my code that runs when users open the database?

Reply to this Message