Merging selected fields from Excel into Access tables

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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.
Click to expand...

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
 
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.
 
Back
Top