chrishorak
asked on
VBA to Browse for Excel file, clean cells and import into Access table
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,
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?
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,
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hmm...
You could use early binding, that should speed it up
rather than
Dim xl as object
set xl = createobject("excel.applic
use
Dim xl as Excel.Application
Set xl = new excel.application
To do this you'll need to add a refrence the the Excel Object Library
In vba window - Tools - Refrences and Select Microsoft Excel Object Library ?.? (pick your version)
Dave
You could use early binding, that should speed it up
rather than
Dim xl as object
set xl = createobject("excel.applic
use
Dim xl as Excel.Application
Set xl = new excel.application
To do this you'll need to add a refrence the the Excel Object Library
In vba window - Tools - Refrences and Select Microsoft Excel Object Library ?.? (pick your version)
Dave
>> When I'm manipulating my temp Excel file - is that being done in memory?
It *SHOULD* be done just like as it would in Excel, except its "hiden" from the user (unless you add a xl.visible = true)
ps. DOnt forget to clean up
ie
xl.quit 'close excel
set xl = nothing
Dave :-)
It *SHOULD* be done just like as it would in Excel, except its "hiden" from the user (unless you add a xl.visible = true)
ps. DOnt forget to clean up
ie
xl.quit 'close excel
set xl = nothing
Dave :-)
ASKER
My only problem now seems to be the removal of the carriage returns/line spaces.
I am looping through all the cells in the worksheet to do the clean (using WorksheetFunction.Clean or Application.substitute) but it takes a very long time in either case.
When I'm manipulating my temp Excel file - is that being done in memory?
Any ideas how to speed it up?