Opening an Access mdb file from Excel VBA

[Layne]

If anyone out there can assist me I will be most grateful. I am trying to
open an Access mdb file from VBA within excel and to further open one of the
macros within the mdb file.

If it helps, the purpose of this is to udpate an Access database from some
extremely large spreadsheets from excel so that I can run some killer
financial reports from it. I find that sum-if and vlookups on such large
amounts of data are cumbersome in excel alone. So, I want to do the calcs in
Access through queries and return them into excel. But, as this spreadsheet
is going to be used by others, I want it to be seemless.

I can accomplish the task if I put the following string into Start/Run:

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Documents and
Settings\Administrator\My Documents\Test Directory\Excel Access Test Db.mdb"
/Excl /X Macro1"

Please help.


Regards,

Layne

 

[Dick Kusleika]

Layne

What does the macro in Access do? There's generally not a lot facility for
running macros in other Office apps because it's better to run all the code
from the source app. Is the Access macro something that you could convert
into an Excel macro and run it from there?

 

[Layne]

Thanks for the resonse Dick. The macro just does the following:
1. Deletes the only table in the db.
2. Imports the same table again.

Effectively, I just want to use Access to manage the large sheet from excel
with queries in order to avoid the time consuming calculation time of Excel.

Have any ideas?


Layne

 

[Dick Kusleika]

Layne

I'm pretty sure you can do that stuff straight from Excel with a reference
set to the Access Object Library. Since you have a command line that works,
you might want to try something first. In VBA there is a Shell statement
that basically mimics the command line. You would use it like

Shell "C:\Program Files\Microsoft Office\Office11\msaccess.exe 'C:\Documents
and Settings\Administrator\My Documents\Test Directory\Excel Access Test
Db.mdb" /Excl /X Macro1'"

However, you have to get the syntax just right particularly with regard to
the double quotes. I'd give you some advice on that but I can never
remember how to do it just right and have to relearn it every time. I'd
guess it should look like the one above, but I can't be sure.

Let me know if that doesn't work and you need help with an Excel macro that
does the same as the Access macro.