Import Range from Excel

Posted on 2007-09-30
Last Modified: 2013-11-27

I have an Excel Sheet with data which i want to import into Access.  These Excel sheets are sent in everyday from employees and are not always in the same location on the harddrive.  Firsltly i need to be able to select which excel file to import.  Once selected the excel sheet does have a Named Range [Data] which contains all the data which is required.

How do i create a procedure/marcro that will import the data in the Named Range [Data] from the selected file.

Hope someone can help.

Question by:d10u4v
    LVL 119

    Expert Comment

    by:Rey Obrero

    this will import the whole sheet

       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
         "TestTable", "C:\MyExcel.xls", True, "Data!"
    LVL 119

    Expert Comment

    by:Rey Obrero
    to locate the excel file you can use

    the standard Windows File Open/Save dialog box

    Author Comment

    Thank you for your response.

    I need to automate the process so that when the user click an import button, they are first asked to select the file and then the process runs the import.  

    How do i use the OpenFile/Save File API within the Transfere Shreadsheet process.

    LVL 119

    Accepted Solution

    <I need to automate the process so that when the user click an import button>
    do you have codes for this , please post

    copy the codes from the link to a module (starting from '**** code start ****)

    create a form with a textbox (txtExcelPath) and a command button

    in the clikc event of the command button

    private sub cmdImport_click()
        Dim strStartDir As String, xlPath as string
        Dim strfilter As String
        Dim lngFlags As Long
        ' Lets start the file browse from our current directory
        strStartDir = CurrentDb.Name
        strStartDir = Left(strStartDir, Len(strStartDir) - Len(Dir(strStartDir)))

        strfilter = ahtAddFilterItem(strfilter, "Excel files (*.xls)", *.xls")

    'this is just to show the path, you can remove if you want
        Me.txtExcelPath = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
                         Filter:=strfilter, FilterIndex:=3, Flags:=lngFlags, _
                         DialogTitle:="Select Excel File")


    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
         "TestTable", xlPath, True, "Data!"

    end sub


    Author Comment

    Thank you so very much.  The code works perfectly.

    All i have done is split the code into two sections and created another button.  The first button selects the file and the second button accepts the file and continues with the import.

    Thank You.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now