[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Update a Linked excel table

Experts,

I have a linked excel table.
It appears fine.
The excel table within Access seems to be locked down and I cant make any edits from within in Access.  

Is it possible to remove this lock down in the linked excel table within Access?  I want to make edits in Access and have those same edits made in the excel file.  I want to move away from making the edits in the excel file.  I have thousands of rows.

thank you
0
pdvsa
Asked:
pdvsa
  • 3
  • 3
  • 2
  • +2
1 Solution
 
als315Commented:
It is not possible to edit linked Excel file. It was possible before Access 2003 (not sure in exact version), now you can do it only from Excel:
https://support.office.com/en-us/article/Learn-how-to-import-or-link-to-data-f4a5b6e6-c98d-43a0-9588-0eab19a75619
0
 
Jeffrey CoachmanMIS LiasonCommented:
I want to move away from making the edits in the excel file
A common alternative is to simply move this data to Access exclusively...

...Then you can link the Access data back to Excel for any Excel specific analysis you might need.

But also note that thee are many tools in Access that can analyze your data to the same level as Excel...
(so again, ..it may be an option to move the entire system to Access)
0
 
Jeffrey CoachmanMIS LiasonCommented:
But with the above setup, you still could not make changes in Excel, and have those changes reflected in Access.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
pdvsaProject financeAuthor Commented:
Thank you.  I plan to move it all inside of access but I am not at that point right now.  

I do however import the data from time to time when I need to make a report.    If I had an ID on the excel side possibly I could use an executable to open the excel file with a docmd.open where ID=ID?
0
 
10023Web site maintenance and designCommented:
You can turn it around and export the data from the access table to the excel spreadsheet and then view the updated export action in access from the external data tab
0
 
10023Web site maintenance and designCommented:
0
 
Jeffrey CoachmanMIS LiasonCommented:
If I had an ID on the excel side possibly I could use an executable to open the excel file with a docmd.open where ID=ID?
...So has your question here changed?
0
 
pdvsaProject financeAuthor Commented:
10023:  I thought of exporting to excel but I would lose the formatting.   I have quite a lot of formatting in the original excel file.
0
 
PatHartmanCommented:
The reason that Access can no longer update a linked Excel Spreadsheet is because MS lost a lawsuit that claimed they used code covered by a patent or copyright.  At the time MS refused to license the code from the original developer and so was required by the court to remove the functionality from Access.  The update was actually to Jet and so affected all versions of Jet.

Now, the only way for Access to update Excel is by using automation or the TransferSpreadsheet method which just pushes out an entire sheet.
0
 
pdvsaProject financeAuthor Commented:
thank you Pat
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now