Import data from Access to Excel containing VB code

[jaclyn.gifford]

Hi!
I am trying to import data from an Access database to an Excel
spreadsheet. I have imported several queries from Access into Excel
already. However the last piece of data that I am wanting to import
into Excel has VB code in the query doing a calculation automatically.
So when I go to Excel to import that piece of data, those queries that
contain the VB code don't even show up as import-able. Is there a way
to import these queries from access into my excel worksheet? I am
using Access 2003 and running on an XP OS. Is it possible to do it in
Excel 2007 if not available in Excel 2003?

I've asked in the Access group and they have sent me here. See below.

Thank you!

I did venture that it is possible to export the query from Access to
Excel.
The query engine uses the Access Expression Service and the Jet Expression
Service to evaluate the function (VBA code). That wouldn't be running
unless
Access is running. While I'm fairly certain you can export from Access,
the
only chance there is for an import is to run Access from Excel via
automation and VBA code.

Thank you. Is there a knowledge base or a link that you could provide
that might have a sample of what kind of VB code would be needed in
order to run from Excel?

Thanks for your expertise!


I can point you to Access automation articles, but I'm sure that
asking in
an Excel newsgroup should be productive.

 

[Shane Devenshire]

Hi,

You can't access them because they need to run in Access to execute the
Access VBA. Using 2007 will not make a difference. One solution is to make
the Access query a Make Table query and run it in Access. The resulting
table will be accessible to Excel.

Another solution you might consider is doing the calculation in Excel or
writing VBA code to pull the data into Excel and in the code launch an
instance of Access....

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[jaclyn.gifford]

Hi,

You can't access them because they need to run in Access to execute the
Access VBA.  Using 2007 will not make a difference.  One solution is to make
the Access query a Make Table query and run it in Access.  The resulting
table will be accessible to Excel.

Another solution you might consider is doing the calculation in Excel or
writing VBA code to pull the data into Excel and in the code launch an
instance of Access....

If this helps, please click the Yes button.

Cheers,
Shane Devenshire












- Show quoted text -

Thank you! I used the make-table option in order to import the data
into Excel. However, the data is statistics for each end of the month.
So I would like the user to only have to refresh all data from Excel.
But because it is a Make-table instead of a query, it doesn't
automatically update. Is there code or another way to run the make
table-query automatically? Or a way to update all the information
including the make table by running a macro every time the user opens
the Excel file?

Thank you

 

[jaclyn.gifford]

Thank you! I used the make-table option in order to import the data
into Excel. However, the data is statistics for each end of the month.
So I would like the user to only have to refresh all data from Excel.
But because it is a Make-table instead of a query, it doesn't
automatically update. Is there code or another way to run the make
table-query automatically? Or a way to update all the information
including the make table by running a macro every time the user opens
the Excel file?

Thank you- Hide quoted text -

- Show quoted text -

I still haven't found a good way around this. Can someone help me?

I used the make-table option in order to import the data into Excel.
However, the data is statistics for each end of the month. So I would
like the user to only have to refresh all data from Excel. But because
it is a Make-table instead of a query, it doesn't automatically
update. Is there code or another way to run the make table-query
automatically? Or a way to update all the information including the
make table by running a macro every time the user opens the Excel file?