Need Macro

R

Richard

I have (4) four Access files and (1) Excel file. I want
to open the Excel file, leave the (4) Access files closed
and start a Macro that will:

1) Open the 1st Access file.
2) Run a Query in Access.
3) Copy and paste the contents of the Query into
Sheet1 of the Excel file starting with cell A1.
4) Open the 2nd Access file.
5) Run a Query in Access.
6) Copy and past the contents of the Query into
Sheet1 of the Excel file starting at the end of the data
in step #3 above.

I want to repeat steps 4 through 6 above for the 3rd and
4th Access file each time pasting the data at the end of
the previous pasted data.

I will end up with an open Excel file and (4) four closed
Access files. Sheet 1 will contain all of he pasted data
from the Queries in each of the access files.

I'm not sure if I should post here or in the Access group,
but I have posted and answered post in this the Excel
group before so though I would try here.

I hope someone can help.

Thanks.
 
D

Dreamboat

My very first thought reading this is that you'd likely be far better off creating a new database, linking those tables, making a query in your new database, and using transfer spreadsheet to dump the data into Excel. I think that'd be a whole lot more efficient

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-
Hope this helps
Anne Tro
Author: Dreamboat on Wor
Email: Dreamboat*at*Piersontech.co
Web: www.TheOfficeExperts.com
 
A

Arvi Laanemets

Hi

At start estimate how much rows will be in every query maximally. Let's
have, that every query'll give you never more as 1000 rows as example.

In some Excel sheet, let's it be Queries, create an ODBC query
(Data.GetExternalData.NewDatabaseQuery and select ODBC driver for Access
database etc.) similar to one you wanted to use on 1st Access database, and
select A1 as point to insert the results into worksheet. Set in queries
properties, that the query is refreshed on open - you can change other
properties too, but don't remove headers for this one for sure.

From 1000th, 2000th etc row insert queries from 2nd, 3rd, etc. Access
databases like the first one, but for set in properties, that NO HEADERS
WILL BE INSERTED, and then reguery them. As result you get a table with
headers in row 1, and data form 1st database strted from 2nd row, data from
2nd database started from 1000th row, etc. - with gaps between every set of
data.

Create a named range p.e. Queries, whitch includes all data you get - down
to row 5000 in my example, and save the excel file but don't close it.
On some other empty worksheet, create an ODBC query using ODBC driver for
Excel files, your Excel file yourself as datasource, and your named range as
table. Set the filter condition for some field(s) [which one(s) depends on
your data] to Not Empty, and select the sorting order. Point to A1 as
starting cell, and set the query to be refreshed on open.

It's done! And no macro is needed for it.
 
Top