Query from 1 excel sheet to 4 similar Excel sheets

[thorgal256]

Hello all, appologies for writting this topic in the Access section but I
thought no one would know how to answer my question in the Excel section.

Here is my problem: I have to work with a database on a excel sheet. This
database is updated by four different companies which all have an excel sheet
with the same layout as the one I use as a database. Every month I am being
sent the 4 excel sheets by the companies and have to copy and paste their
data in my database. I know this way of working is outdated and am trying to
find a better solution. I heard Access could allow me doing something better
but do not know how to use it and have no time to learn.

I have seen that I could use the query function in excel to automatically
update my excel master sheet, but this only works with one source file. I
would like to make a query which will update my excel document from four
excel files by pulling data from the four companies excel sheets.

so for example, on the foor sheets called S1,S2 , S3 and S4 i would have
these figures in my database,
S1 cell A1: red,
S2 cell A1: green
S3 cell A1: white
S4 cell A1: blue
What i would like to do is by just making a query on my Master Excel sheet
to have the info with this layout:
Master excel sheet, cell A1: red,
cell A2: green
cell A3: white
cell A4: blue

I know it must be pretty basic and lack vocabulary for people used to
database processing but it's the first time I am doing it and have no one to
show me.
Thanks for your help

 

[Klatuu]

Start with the TransferSpreadsheet Action. It is used to import or export
Excel spreadsheets.
I would suggest you start by learning macros.
You will also need to learn a little bit about queries.
Here is the basic idea.
First figure out how to use the TransferSpreadsheet Action in a maco to
import the 4 individual spreadsheets. You can do that by creating a macro.
In the macro design view, yo can choose TransferSpreadsheet in the Action
column. Then in the text boxes below, Choose Import ad the TransferType.
The spreadsheet type probably doesn't need to change from the default. Now
give it a table name. This will be the name of the table in Access that will
contain the data imported for Excel. Then enter the file name of the
spreadsheet. I would suggest you always use the same file name and folder
for every import. I don't mean that S1, S2, S3, and S4 should use the same,
I mean have a consistent name for S1, another for S2, etc.
Has Field Names means does your Excel sheet have columns names in the first
row? hopefully it will. It makes life easier. Range is for if you don't
want to import everything. In most cases, leave it blank.
Now, add a line to the macro for each of the 4 sheets you will import.

Now, you have 4 tables - one for each company, but you want to combine the
data. To do this, you will want 4 Append queries, one for each table. It
will be use to collect the data for all 4.

To start, you will need to create a new table. You can do that easily by
first importing your 4 tables, then making a copy of one of them, give it a
different name, and delete all the data from it.

Now, to create the queries.
Create a New query in Design View.
You will get a Show Table dialog that shows all the tables and queries in
the database. Select S1 and Click Add. It will now show up in the top
portion of the query builder. Close the dialog.
In the table definition, the first row will show an *. Double click on that
and in the first column of the grid in the Field: row you will see the name
of the table followed by .* This means you want to return all columns in the
table.
At this point it is a select query. If you run it (Click the red ! on the
task bar), you will see the imported data. We need to make it an append
query.
Right Click in the grey area where the table definition is and select Query
Type, then select Append. You will get an Append dialog. Enter the name of
the new table you created to hold all the data.
Save the query. Now make a copy for each of the 4 tables.
If you like, you can create a macro to run the queries. Use the RunSql
Action.

Once you have this done, you now have all the data in one table.
Now you can export the data into a single spreadsheet. Create another
TransferSpreadsheet macro, but this time, it will be an Export rather than an
import.

You won't get it in the order or format you described, but what you will get
will be all the data in one spreadsheet. To do what you described can be
done, but it may be a bit advanced for you at this time.

Try this first. Good Luck

 

[John Nurick]

Recent versions of Access have no simple way of updating Excel sheets,
and since your objective appears to be to consolidate information from
four Excel sheets onto a fifth you may do better to ask in an Excel
forum.

One thing you can do with Access is run a union query that gets data
from several sheets and writes it to a new worksheet, using this
syntax (which I've just tested on my machine):

SELECT * INTO
[Excel 8.0;HDR=Yes;database=C:\Temp\Access\New.xls;].[Sheet1]
FROM (
SELECT * FROM
[Excel 8.0;HDR=Yes;database=C:\Temp\Access\Table1.xls;].[Sheet1$]
UNION
SELECT * FROM
[Excel 8.0;HDR=Yes;database=C:\Temp\Access\Table2.xls;].[Sheet1$]
UNION
SELECT * FROM
[Excel 8.0;HDR=Yes;database=C:\Temp\Access\Table3.xls;].[Sheet1$]
);

Note that there is no "$" in the destination sheet name.