Importing Excel - User selectable file

[mpm32]

I've tried to import an excel file into my access DB. I used the transfer spreadsheet method but the user needs to be able to select the file that they want to import.

I tried this;

Dim fDialog As Office.FileDialog

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    'Do not allow user to make multiple selections
    .AllowMultiSelect = False
    'Set the title of the dialog box
    .Title = "Please select Sales file to import."
    'Clear out the current filters, and add our own.
    .Filters.Clear
    .Filters.Add "Text", "*.txt"
    'show the dialog box.  If the .show method returns "true", the user picked
    'at least one file.  If the .show method is "false", the user clicked Cancel.
    If .Show = True Then
        For Each varFile In .SelectedItems
        'This pulls out the file name from the path string
        Dim ReversedString As String, FirstFind As Integer
        ReversedString = StrReverse(varFile)
        FirstFind = InStr(ReversedString, "\") - 1
        FileName = StrReverse(Mid(ReversedString, 1, FirstFind))
    Next
Else
    MsgBox ("You either hit Cancel or did not select a file.  Please try again.")
End If
End With

but I'm getting an error on the word FileDialog in the line Application.FileDialog(msoFileDialogFilePicker).

I have selected the reference for Microsoft Office 12.0

I have looked for a solution for this but most of the solutions I have tried are from years ago and look like they are applicable to 97. I am using 2000.

I am trying to call this from a button click.

Any suggestions?

Thanks.

 

[lespaul]

Here's one option:

Private Sub Command0_Click()
   Dim strFilter As String
    Dim strInputFileName As String
        
    strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mdb)", "*.MDB")

    strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, InitialDir:="", _
                    DialogTitle:="Please select 007_Datenbank...", _
                    Flags:=ahtOFN_HIDEREADONLY)


End Sub

I think the function you are trying to use can be used for either a file or a folder, since you're only using a file I think the above solution will work for you.

(taken from

http://p2p.wrox.com/topic.asp?TOPIC_ID=22061)

Leslie

Have you met Hardy Heron?

 

[RoyVidar]

The FileDialog thingie became available in the 2002 (xp) version.

The one I'm linking to, can be used in any version including 2007

http://www.mvps.org/access/api/api0001.htm

Roy-Vidar