G'Day,
I have a bit of a problem.......the objective is to create a single table called 'tbl_MasterStockFile' in MS Access from importing multiple spreadsheets that are located in seperate workbooks.
(BTW...The column structure is the same in all spreadsheets with the first row being the field name).
I need help to achieve the following which is initiated from a command button called 'Import Stock/Products':
1. Delete all tables that have the a name that contains 'tbl_import'
2. Provide a File/Open dialogue allowing the user to multi select one or more Excel spreadsheets to import, very much like the single select code here:
http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=openfile+dialog+access
2.Once the user has multi selected one or more spreadsheets then (Using the 'Transferspreadsheet' command) a table will be created per spreadsheet, with the spreadsheet first row = field names ie ('Has Field Names = Yes')
The table names need to be constructed as follows:
tbl_import_filename
filename = spreadsheet filename without .xls extension.
3. Each table (tbl_import_filename) requires the blank records removing (The Transferspreadsheet command seems to bring blank records across ?)
4. All the records in all of the (tbl_import_filename(s)) tables need to be merged and the duplicates removed. Duplicate records based on the column in the spreadsheets called 'StockID/Barcode' would be used as this field should be unique.
5. A table needs to be created called 'tbl_MasterStockFile' and the merged (tbl_import_filename(s)) without duplicate records needs to be imported into the 'tbl_MasterStockFile'.
Can anyone help ?
I have a bit of a problem.......the objective is to create a single table called 'tbl_MasterStockFile' in MS Access from importing multiple spreadsheets that are located in seperate workbooks.
(BTW...The column structure is the same in all spreadsheets with the first row being the field name).
I need help to achieve the following which is initiated from a command button called 'Import Stock/Products':
1. Delete all tables that have the a name that contains 'tbl_import'
2. Provide a File/Open dialogue allowing the user to multi select one or more Excel spreadsheets to import, very much like the single select code here:
http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=openfile+dialog+access
2.Once the user has multi selected one or more spreadsheets then (Using the 'Transferspreadsheet' command) a table will be created per spreadsheet, with the spreadsheet first row = field names ie ('Has Field Names = Yes')
The table names need to be constructed as follows:
tbl_import_filename
filename = spreadsheet filename without .xls extension.
3. Each table (tbl_import_filename) requires the blank records removing (The Transferspreadsheet command seems to bring blank records across ?)
4. All the records in all of the (tbl_import_filename(s)) tables need to be merged and the duplicates removed. Duplicate records based on the column in the spreadsheets called 'StockID/Barcode' would be used as this field should be unique.
5. A table needs to be created called 'tbl_MasterStockFile' and the merged (tbl_import_filename(s)) without duplicate records needs to be imported into the 'tbl_MasterStockFile'.
Can anyone help ?