Problem with ADO or DAO?

[aner1755]

Hi All,
I’ve got a problem to make use of some functions I found in a reference book. I believe that the VBA code I found is current and up to date. I’ve posted me question to the author but it would be nice to see what you all think about this…
Please take a look at this extract from my mail;
At page 189 in Access 2007 VBA Bible there is some VBA code that would be handy for me (the function OpenSpecificWorkbook). I have tried to imply this to my Access project but encounter an error when executed. The problem origins from the line “Dim appExcel As Excel.Application”, and VB complains about a compiling error. VB states something like “Compiling error: Own-defined type has not been defined”. For more information you can look at the attached file (I’m sorry that the Access interface is in Swedish).
I believe this is due to some missing database, module, .dll-file or something like that. Do you know what is needed, where to get it and how to imply it?

Yours sincerely
Anders Eriksson

 

[chergh]

You've not added a reference to the excel object library.

Goto Tools>References and add the "Microsoft Excel X.0 object library"

 

[JANR]

Or you can use lateBinding an set the reference during runtime.

Dim oApp as object
oApp = CreateObject("Excel.Application")

Do you thing........

oApp.Quit
Set oApp = Nothing

JR

 

[aner1755]

Hi chergh and JANR!
Thanks for your replies, I got JANRs proposal to work. Very nice! But I’m still curious about cherghs strategy that I didn’t get the hang of…

First, is it in Access or Excel the reference should be added? I guess Access.

I tried to localize the path for the “Microsoft Excel X.0 object library” by checking the path of my already installed COM-references (see attached file, it seems like I don’t got any Access-references… ) without luck. Is it because I don’t have the package or just that I can’t find it? I’m looking for a .dll-file, right? Or is it a .accda, .accde, .mda, .mde or .exe-file?

Should “Microsoft Excel X.0 object library” come with the installation of Access or is it something I got to download? Do I have to pay for that?

Cheers!

 

[JANR]

To set the library:

Open a access module codewindow. (Ctrl+G -> shortcut)
On the toolbar choose Tools and then Refrence.

You will now see the diffrent library that is installed on your system, scoll down until you locate Excel and check the higest version number and close

This is called Earlybinding and is ok if all who use you db has the SAME Office version, if one has a different version then your function CAN breake. Latebinding is a way to go if this could be a problem, a few extra lines of code but it isen't gonna bite you later.

JR

 

[aner1755]

I followed your instructions but am still not getting it. Please take a look at the attached file where you can see that there are no packages to choose from when I try to add the reference (since you are located in Norway I hope my Swedish interface won’t disturb you much…).
Would you be kind to point me to your folder where you have your file (pathname)? Then I could try to find my opposite. Or maybe the name (with file extention, i.e. .dll, .mda…) of the file is even better… But both is best. :-D
When I try to add a reference to Access from the main menu I have very few additions to choose from. But the same procedure in Excel gives me a lot options…
Thanks very much for your quick response and good advice regarding Earlybinding! :-D

 

[JANR]

The refrence is NOT an addin, you should have it built in already. Try this, goto your module "Import2" and open it in DESIGN view. Now you should be in an access codewindow. Look on the menubar and locate Tools. I'm using Access 2003 but I don't think they(microstuff) have moved it. If you find tools just open Refrence of "referanse" in my language.

JR

 

[JANR]

See attachment on how it looks in 2003, it under tools as you can see i have checked only a few refrences.

JR

 

[aner1755]

Perfect! :-D
I found it all thanks to you, JANR.

Have a blessed day!

 

[JANR]

Your welcome.

JR