VBA to Browse for Excel file, clean cells and import into Access table
Posted on 2004-11-17
I'm not sure if it is possible to do the following from within Access. I usually use Visual Basic 6, so I'm not sure what I can do in VBA.
I would like to have a form that contains a control to select an Excel Spreadsheet. The spreadsheet should then be loaded into a table in Access.
I can load the spreadsheet into the table by using DoCmd.TransferSpreadsheet, but only with a hard coded value for the spreadsheet location.
If it is not possible to browse for the file, then I could use a text field to type in the location of the spreadsheet, but the first option is preferable.
A further complication is that the spreadsheet contains some carriage return characters in some of the cells, which I need to clean up so that I get all the data across to the table. (Currently all data in the cell, after the carriage return, is lost). I could do this from Excel, but unfortunately I do not have control of the spreadsheet, so I can't use macros in the spreadsheet.
I'm using Access and Excel 2002
Is this possible, and if so, how would I do it?