Need a function to select a file, before importing from Excel

[neelsfer]

Currently i use the TransferSpreadsheet acImport method, to import an Excel file called "Athletedata.xls", into a table called "Cyclist".
I would now like to select the file from any directory, instead of having to import it from a specific directory only.
I use this code currently to import the Athletedata.xls file. Please assist

 DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 10, "Cyclist", "c:\RTBU\AthleteData.xls", True, ""
 

[NeoPa]

See Select a File or Folder using the FileDialog Object. If you need more help after that then let us know.

[neelsfer]

When i use your code and module, it opens up at i can select the Excel file using this code, but i do not get it to import data. Where do i go wrong?

  Dim strPath As String
 
    With Me
        strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
        strPath = FSBrowse(strStart:=strPath, _
                           lngType:=msoFileDialogFilePicker, _
                           strPattern:="MS Excel,*.xls")
        If strPath > "" Then .lblFile.Caption = strPath
         DoCmd.SetWarnings False
     DoCmd.TransferSpreadsheet acImport, 10, "Cyclist", "", True, ""
 
    End With

[NeoPa]

You don't actually use the result returned (strPath) in your TransferSpreadsheet line (#10).

It should look more like :

    Dim strPath As String
 
    With Me
        strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
        strPath = FSBrowse(strStart:=strPath, _
                           lngType:=msoFileDialogFilePicker, _
                           strPattern:="MS Excel,*.xls")
        If strPath > "" Then
            .lblFile.Caption = strPath
            Call DoCmd.SetWarnings(False)
            Call DoCmd.TransferSpreadsheet(acImport, _
                                           acSpreadsheetTypeExcel11, _
                                           "Cyclist", _
                                           strPath, _
                                           True)
            Call DoCmd.SetWarnings(True)
        End If
    End With

[neelsfer]

Thx Neopa for the trouble. My data would not import until i changed acSpreadsheetTypeExcel11, to acSpreadsheetTypeExcel9. It is Now working fine

[neelsfer]

Neopa. I decided to create a standard template in Excel with fixed titles. It works great except for one problem - It creates lots of empty rows. I sorted that with a delete query to get rids of blanks, when viewing the data. Duplicates entries are not an issue because one of my fields prevent duplicates, and the "setwarnings" function keeps the system quiet.
Thx again for the excellent service !!

[NeoPa]

NeelsFer:
My data would not import until i changed acSpreadsheetTypeExcel11, to acSpreadsheetTypeExcel9.

My bad Neels. That was a guess as I'm working on 2003 myself and only have up to acSpreadsheetTypeExcel9.

As for your Excel template, try opening it, selecting all rows from where the worksheet should end (I'm guessing row #2) to the bottom and deleting them (Using right-click / Delete rather than the Del key - which simply clears their contents). Once this has been saved it should work better as a template for you.