Edit Linked Excel Data in Access

[isladogs]

One of the main advantages of Access is its ability to read and edit linked data files from a wide variety of sources.

Many developers are therefore surprised that linked Excel, CSV and other text files are READ ONLY.
In fact that wasn't always the case. Until 2002, all it was possible to edit both Excel & text files.
However, in 2002 Microsoft lost a copyright infringement case which impacted on the interaction between Access and Excel.
As a result, Microsoft removed the ability to edit those linked files from Access and the functionality has never been restored.

For the past 20 years, the following has been true:
a) Records in linked Excel tables are READ ONLY. Records can NOT be added, edited or deleted.
b) Records in linked CSV, TXT files cannot be edited or deleted. However NEW records can be added.

As a result, most developers either use automation or import the data, edit then re-export (or paste back) to Excel.

However, there are two other solutions that are not widely known.

METHOD 1
Create a query to the external Excel file and set IMEX = 0 (instead of the default value 2). For example:

SELECT XL.*
FROM (SELECT * FROM [Sheet1$] AS xlData IN 'G:\MyFiles\ExampleDatabases\Excel\tblData.xlsx’
[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]) AS XL;

I am very grateful to fellow AWF member @CJ_London for reminding me about the first method in this thread at Access World Forums back in June 2021:
Linked (Excel) Table - multiple users at a time unable to query

METHOD 2
However, there is an even simpler method which I stumbled across yesterday by chance.
In recent versions of Access (2021/365), you can edit the connection string directly from the new linked table manager (LTM).
Once again, set IMEX = 0 instead of the default value 2

Excel 12.0 Xml;HDR=YES;IMEX=0;ACCDB=YES;

. . . and BINGO => direct editing of linked Excel tables is now possible!

NOTE: If you have older versions of Access, you cannot edit connection strings using the LTM
However, you can of course still do so using VBA.

This feature almost completely removes the need for automation or other work-rounds.
You can edit existing records and add new records. However, you still cannot delete existing records.

NOTE: These methods do NOT work for CSV or TXT files

For more details, see my web article:

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

Or watch the video I uploaded to YouTube:

 

[CJ_London]

There are a couple of things on your website I don't believe to be correct and I've PM'd you to explain. Not included here because not mentioned here.

If you attempt to edit an excel cell from access to convert text to a number with a view to making the column numeric, it won't work - excel will store the value as text - sometimes the value as typed, sometimes some other value if your are trying to correct a #num error. If the column has been defined as text, your numeric value will be stored as text- see this edit from access of a value from 3 to 4.

and trying to correct a #num error for a numeric column

the value posted has been taken from another row and column in the table and is not what I tried to correct the value to

 

[isladogs]

Thanks.

I've now amended the webpage comment re: IMEX=1

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

www.isladogs.co.uk

I also found an interesting and unexpected quirk.
Using HDR=No, the first row should not be treated as a header. However, if HDR=No and IMEX=0, the first row is still treated as a header
This could cause problems!