- Local time
- Today, 18:21
- Joined
- Feb 19, 2013
- Messages
- 17,130
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
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
] or if the first row is blank [sheet1$A2] or just a few rows [sheet1$A15]
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$A15] 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.
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
] or if the first row is blank [sheet1$A2] or just a few rows [sheet1$A15]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
15] 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.
