Opening an Access mdb file from Excel VBA
 

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

Opening an Access mdb file from Excel VBA
 

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?


--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Layne wrote:
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

Opening an Access mdb file from Excel VBA
 

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" wrote:

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?


--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Layne wrote:
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

Opening an Access mdb file from Excel VBA
 

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.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Layne wrote:
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" wrote:

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?


--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Layne wrote:
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