Excell Update when linked to access

[Guest]

I have 4 excell files with the same format and all linked to a single access
db. The problem I have is that the excell files are not updating
automatically when being linked. (the 4 files all point to other tracking
systems). Is there a way to update the excel sheets from access after the
link occurs?

 

[Guest]

Not any more. Microsoft lost a law suit and it required them to disable the
ability to update Excel spreadsheets when linked as a table. There are three
alternatives:
1. Use MsQuery to create a query in Excel that will pull the data from Access.
2. Use the TransferSpreadsheet method to replace the existing data in the
spreadsheets.
3. Use Automation to open the spreadsheets as an Excel object and manipulate
the data programmatically.

 

[Guest]

Klatuu, thanks for the update, can you provide more info on options 2 & 3
please?

 

[Guest]

Option 2 is pretty straight forward. After you have made the updates to the
Access table(s), use the DeleteObject method the destroy the link to the
spreadsheet. Then use a query or, if it is a single table, the table to do a
TransferSpreadsheet. If the spreadsheet already exists, you will be prompted
as to whether you want to replace the existing spreadsheet.

Option 3 is more code intensive, and should only be considered if there is
some reason Option 2 can't be used. If you are not familiar with
manipulating an Excel Application object in Access, there is quite a learning
curve. If Option 2 will not work for you, post back and I can provide some
sample VBA code that does this.

 

[Guest]

Ok I am with you, but I think your looking at my problem backwards. I will
do all data entering in excel workbooks 1 - 4. Then we have workbooks 5 - 8
that pulls only the data we want into them in like formats. This works
great, but the only way we can get workbooks 5 - 8 to be updated is when we
open each and say yes to the update link question. What we are trying to do
in access is to link or wb 5-8 into access and skip our step 2. I can link
and pull the data now but I am not getting the latest data, only the
availabel data from the last time someone opened the wbs 5-8. We will not be
changing the data in access, just trying to view it all in one place. Hope
this helps you understand better because I am still interested in your step 2.