Access-Excel. How to edit an excel file from Access using SQL (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 00:08
Joined
Feb 19, 2013
Messages
16,553
As I'm sure you are aware, if you create a linked table to an excel sheet/range you can sort it, filter it, join it to other tables. What you can't do is edit the data. In many cases this does not matter since the requirement is to import the data or use as a reference.

However on occasion there is a need to edit the data which is usually accomplished using VBA. But there is a way using SQL.

The basic construction of the query is this

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

Substitute sheet1$ and C:\pathtofile\filename.xlsx with your requirement and open the query. Not specifying a range will open a range which matches the populated columns and rows (i.e. the range where bottom right is found by using end>home in Excel), not 255 columns x 1m rows. Range can be specified as [sheet1$A:D] or if the first row is blank [sheet1$A2:D] or just a few rows [sheet1$A:D15]

As with a linked table, you cannot mix data in a column - text in a numeric column will show as #num! - but at least you can correct or blank it before trying to import.

Things you can't do is delete a row or add another column (but then you can't in a linked table), but you can add new rows, delete or modify data. Note you cannot add rows to a filtered query or where a range displays fewer rows than are in the excel table. So if there are 20 rows and your range is [sheet1$A:D15] you cannot add another row but if there were only 10 rows, they will all be displayed and adding the next record will be added to row 11, not row 16.

The reason this works is because the IMEX setting=0 (export), the other settings are 1 (import) and 2 (linked), both of these settings are read only. I believe in the early days of Access, some 25+ years ago, IMEX=2 did allow editing but there was a copyright or patent lawsuit which meant it had to be disabled.

Not recommended as it is not completely stable, but you can even have the excel file open at the same time, effectively sharing data (open excel before opening the query otherwise excel will open read only). Changes made in the query will instantly update excel, changes made in excel update the query when the query receives back the focus. As I said, not completely stable, but a quick way of testing how things are working.

I have not experimented with multiple users opening the query at the same time, or another user have excel open at the same time - my expectation is that the query would cease to be editable. If you do have excel open and make changes via the query, you will be prompted to save when you close Excel. If you don't save, the changes will be lost.

So now instead of an uneditable linked table we have an updateable query that can be used for append and update queries in the usual way. You cannot delete rows so a delete query will not work. Not tried a make table but I doubt it would work since you need to specify an existing table, so at least the headings need to be there - specifying a blank worksheet with or without a range will give you a single column.
 

Bullschmidt

Freelance DB Developer
Local time
Yesterday, 19:08
Joined
May 9, 2019
Messages
40
Nice handy example as it sure seems odd that can't just do it from a linked table...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:08
Joined
Feb 19, 2013
Messages
16,553
they did - MS had Multiplan and were commissioned by Apple to develop a spreadsheet program (called Excel) for their machines.

Pre (Apple) Excel, I actually developed a very simple spreadsheet for the apple2IIe in pascal. It only had a few rows and columns (to fit an 80 x 40 char monitor - no windows in those days, no mouse, no scrolling) it only did basic maths and I used it for product costing. Data was stored in a table - with pascal you could have variant table structures.

see this link

and towards the bottom

Early history
Microsoft originally marketed a spreadsheet program called Multiplan in 1982. Multiplan became very popular on CP/M systems, but on MS-DOS systems it lost popularity to Lotus 1-2-3. Microsoft released the first version of Excel for the Macintosh on September 30, 1985, and the first Windows version was 2.05 (to synchronize with the Macintosh version 2.2) in November 1987.[77] Lotus was slow to bring 1-2-3 to Windows and by the early 1990s Excel had started to outsell 1-2-3 and helped Microsoft achieve its position as a leading PC software developer. This accomplishment solidified Microsoft as a valid competitor and showed its future of developing GUI software. Microsoft maintained its advantage with regular new releases, every two years or so.
 

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 20:08
Joined
Apr 27, 2015
Messages
6,286
Thanks CJ, that is an interesting tidbit.
 

Users who are viewing this thread

Top Bottom