Opening Access from Excel and vice-versa

[tryder]

This is my first post here, and maybe my third anywhere. Feedback welcome.

Here's my pboblem:
I'd like to write vb code in an Access module so that I can open a closed Excel workbook (in a file on one of my drives), retrieve data from a worksheet and take it back to to my Access module. I don't want to import or link the worksheet.

Id like to be able to reverse the process, that is, from the vb code for an Excel macro, I want to write code to open a closed Access database (on my computer), open a table or run a query, grab some data and go back to my Excel workbook and macro.

I just need the code for opening the application, I know what to do once I get inside.

I've tried the code from "Mr. Excel on Excel" (pp431-432) and I get an "Object Needed" (or something like that) message.

Thanks in advance.
Terry

 

[WillR]

You need to set a reference to the Excel Object library in the Access VisualBasic editor (Tools menu in the VBE)

Likewise in Excel you need to set a reference to the Access Object library from the Excel VBE

See my note on how to do it here (a Word example but the same principal applies)

http://www.mrexcel.com/board2/viewtopic.php?t=75176

Hope this helps

 

[tryder]

Setting the references was the key.
Thanks,
tryder