Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2267
  • Last Modified:

Import Range from Excel


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.

  • 3
  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:

this will import the whole sheet

   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\MyExcel.xls", True, "Data!"
Rey Obrero (Capricorn1)Commented:
to locate the excel file you can use

the standard Windows File Open/Save dialog box
d10u4vAuthor Commented:
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.

Rey Obrero (Capricorn1)Commented:
<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

d10u4vAuthor Commented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now