Update a Linked Excel Table

[PicoTTS]

Hi,

I was wondering if there was a way to use Access to update values in an excel file?



I connected a linked excel sheet and when I try to edit any data, I get a message that says "This RecordSet is not updateable". I was hoping to use an update query in access to update the values in my excel file.

Thanks

[ranman256]

you cant update, but you can export via COPYFROMRECORDSET.
or
post directly to the cell by opening control of excel.

Private XL As Excel.Application


Set XL = CreateObject("excel.application")
with xl
  .Visible = true
  .workbooks.open vFile
  .Range("a1").value = "my new value"
end with

[CJ_London]

you can edit using a query rather than a linked table

if your query is something like this

SELECT *
FROM [sheet1$] AS xlData IN 'C:\pathtofile\myxlfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]

Note the IMEX setting must be 0 which puts the query into export mode. (IMEX=1 is import mode, IMEX=2 is linked mode) - import and linked modes are not editable.

You can even have the excel file open at the same time (open it before you open the query otherwise it will open readonly) - edits in the query are immediately visible in the excel file, edits in the excel file are visible in the query when it receives back the focus. I've only tested this with me opening both - don't know if it will work if someone else has the file open or multiple access users are trying to edit the same excel file at the same time.

I tend not to use linked tables any more (in relation to excel, csv and other access files) using queries like the above built in vba as required and often linked to other tables (for example using left joins to importing only new data)

Note that using sheet1$ only brings through the populated columns and rows (i.e. the end|home range), you can specify ranges and range names if required

edit: if you want to append, you need to save the above query (call it say qryXL), then you can use it in an append query