ExcelToAccess.mdb |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 09 Jul 2008 at 10:54pm |
1 - This sample demonstrates transfer of data from excel workbook to access db, accompanied by updating of data in local table of access db. 2 - Both techniques, i.e. Link as well as Import are covered. 3 - Data blocks can be located anywhere on any of the worksheets, so long as it is ensured that each such block comprises of specified number of columns (as per global constant) and first row represents the column headings. 4 - For transferring any particular data block to access db, select the worksheet on which it is located. Shortcuts for execution are: (a) Via Link to access: Ctrl + k (b) Via Import into access: Ctrl + t 5 - As soon as any of the above shortcuts is applied, data block on active worksheet gets identified automatically, accompanied by assignment of dynamic range name to it (as per specified global constant). In case of (b) above, the cell denoting heading of first column of data block should be in selected state, before using the short cut. In case of (a) above, even this is not necessary. First column heading, wherever it is, gets identified automatically. 6 - Based upon linked or imported data, updating of local table in access db takes place on the following lines: (a) If PartNum already exists in the local table, the date & stock get updated as per fresh data. (b) If fresh data has records with PartNum values not yet existing in local table, corresponding fresh records get appended to the local table. 7 - After each execution of data transfer, the access db gets displayed adjacent to excel workbook. Records freshly appended get highlighted in light blue, while those freshly updated are shown in dark yellow. Simultaneously, in excel workbook too, the data rows responsible for causing changes in access local table (update/append) get correspondingly highlighted. 8 - There is complete flexibility regarding the worksheets. Any worksheet (even a freshly inserted one) can be used, provided it is the active sheet. 9 - Even the name of workbook or the range name assigned dynamically to the data block on active sheet, do not matter, so long as it is ensured that the workbook is housed in the same folder that contains the access db. 10 - It is desirable that the same computer should not have multiple versions of Excel installed. Moving amongst different versions of excel on same computer in the same session can lead to unexpected results. 11 - The sample has been tested with Excel 2000 workbook in conjunction with Access Db in Access 2000 file format in various installations of access, i.e. 2K, XP and 2003. Note - All user action is via excel workbook. Access Db need not be opened directly. References: (a) Excel Workbook - Access 9.0 Object Library & DAO 3.6 (b) Access Db - DAO 3.6
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |