Merging selected fields from Excel into Access tables

G

Guest

Surely it can be done, but heck if I know how!

I have to export report results into Excel spreadsheets, due to the client
not having Access. I'll then need to take their updates and put them back
into Access. Twice.

The clunky way of doing this is to export *all* fields of my 2 tables that
will be later updated, and only pull the ones that the clients need to update
into a pretty template (and lock the rest up). When I get it back, I'll use
another spreadsheet that pulls some numbers from the locked data and the new
data from their template, and makes it into 1 complete record which I can
then export back to Access. Once I have all results back, I can swap my
temporary table for the real table.
Not pretty, but it'll work.

Tell me there's a way instead to have a query that looks at a certain place
on the Excel spreadsheet, finds the correct record in my Access table based
on 2 other cells, and pulls in *only* that field's info, rather than having
to basically reconstruct the record in Excel. And this with the fact that
I'll have 221 differently named Excel spreadsheets to pull data from.
 
A

Andi Mayer

Surely it can be done, but heck if I know how!

I have to export report results into Excel spreadsheets, due to the client
not having Access. I'll then need to take their updates and put them back
into Access. Twice.

The clunky way of doing this is to export *all* fields of my 2 tables that
will be later updated, and only pull the ones that the clients need to update
into a pretty template (and lock the rest up). When I get it back, I'll use
another spreadsheet that pulls some numbers from the locked data and the new
data from their template, and makes it into 1 complete record which I can
then export back to Access. Once I have all results back, I can swap my
temporary table for the real table.
Not pretty, but it'll work.

Tell me there's a way instead to have a query that looks at a certain place
on the Excel spreadsheet, finds the correct record in my Access table based
on 2 other cells, and pulls in *only* that field's info, rather than having
to basically reconstruct the record in Excel. And this with the fact that
I'll have 221 differently named Excel spreadsheets to pull data from.

I do a similar thing with automatisation
I have a template which I fill with my tables, change the formulars on
the sheets and send it to the customer.
He/She sends it back with his/her information in it and I take with
the automatisation the necessary changes into my tables
The sheets have a versionNumber and a custumerNumber.

The only disadvantage is that this process is slow, because I have to
write 1000 to 2000 Excel cells, reformat them.

The reading is not that bad, because I only have to check around 100
cells for the changes.

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
J

John Nurick

Hi Rose,

Here's one way:

1) Export only the fields that need to be updated plus the primary key
field(s). After exporting, it would be a good idea to protect the Excel
worksheets so the users can update the data that needs updating but
can't change the primary key values.

2) When you get the data back, access the worksheet as a linked table.
Then use an update query that joins the worksheet to your Access table
on the primary key and updates the Access table with the new values from
the worksheet.

You can short-circuit (2) by using a SQL query that connects directly to
the worksheet without needing to set up a linked table. It will look
vaguely like this:

UPDATE
[Excel 8.0;HDR=Yes;database=C:\Temp\A.xls;].[Sheet1$]
INNER JOIN AccessTable
ON [Sheet1$].PK = AddrCent.PK
SET AccessTable.Field1= [Sheet1$].[Field1],
AccessTable.Field2= [Sheet1$].[Field2]
;

To pull the data from 221 different workbooks or worksheets, just get
their names into a table and then write code that works through the
table assembling and executing a SQL UPDATE statement for each.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top