inporting Excel to Access 2

[cneill]

Hi
I want to inport an excel spreadsheet into access
I can use this code, but the file path to the spreadsheet is not always the same, so I would like to open the windows dialoge box to select the file to inport

DoCmd.TransferSpreadsheet transfertype:=acImport,tablename:="Hours Worked", filename:="C:/Personnel/Hours Worked.xls",HasFieldNames:=True

How do I do this?

 

[PHV]

A starting point:

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
  .Title = "Please select the spreadsheet"
  .Filters.Clear
  .Filters.Add "Excel files", "*.XLS"
  .Filters.Add "All Files", "*.*"
  .InitialFileName = "C:\Personnel\"
  If .Show = True Then
    For Each varFile In .SelectedItems
      DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="Hours Worked", filename:=varFile, HasFieldNames:=True
    Next
  Else
    MsgBox "You clicked Cancel in the file dialog box."
  End If
End With

NOTE: you need to reference the Microsoft Office x.y Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cneill]

Hi PHV (MIS)

Perfect, this is the first step, next steps to follow

Thanks CNEILL

 

[cneill]

Hi PHV (MIS)

I would like to give them a Measage
Transfering Data...
then
Tranfer Complete...

I don't want any ok buttons on the message

Thanks for your help

 

[boblarson]

I would like to give them a Measage
Transfering Data...
then
Tranfer Complete...

I don't want any ok buttons on the message"

Create your own form and set it to be POPUP and open it just before the code that starts the import and then close it after the import code.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com