VBA to Browse for Excel file, clean cells and import into Access table

Posted on 2004-11-17
Last Modified: 2012-08-14
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?
Question by:chrishorak
    LVL 34

    Accepted Solution

    get file name :->

    Now for the other bit...

    You can manipulate Excel from Access just like you can in VB 6.0 (VBA isnt all that different)

    My thoughts...

    Get file name
    Copy the file using   Filecopy source, destination  to create a "temp" copy of the excel file so we can "play" with it

    Use CreatObeject("excel.application")  (or use early binding, your choice) to get an excel object (like in VB)

    Manipulate your "temp" excel file to parse out the hard returns
    Save temp file
    Use docmd.transferSpreadsheet to import it
    Delete temp file using Kill filename


    What do you need help with??


    Author Comment

    Thanks Dave - that is very useful

    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?
    LVL 34

    Expert Comment


    You could use early binding, that should speed it up

    rather than

    Dim xl as object
    set xl = createobject("excel.application")


    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)

    LVL 34

    Expert Comment

    >> 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


    xl.quit 'close excel
    set xl = nothing

    Dave :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now