Select Excel File for Import with Form Button

With Access 2007, I have successfully used this code in the past to open a Browse... window for importing Excel files with a form button.


Private Sub SelectSurvey_Click()

    Dim strStartDir 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")
    Me.txtFileName = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
                     Filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
                     DialogTitle:="Select File")
        

End Sub

Open in new window


However, I am now using Access 2010 and I receive the error "Method or data member not found" in association with "txtFileName".

I am not sure if it is the newer version of Access (or something else) that is causing this.

Does anyone have a suggestion for how to get this function working? I am willing to try a whole new set of code if that is what will be necessary.
LVL 1
dougf1rAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you sure you have the necessary functions to use this code? For example, you must have a function named ahtCommonFileOpenSave in a Standard Module.

See this link for more information: http://access.mvps.org/access/api/api0001.htm
0
gracie1972Commented:
I would try to import the spreadsheet first (You can delete after) then save the import name as "ahtCommonFileOpenSave" at the last step (check Save Import Steps).  I had that same issue before and that was a work around.
0
Rey Obrero (Capricorn1)Commented:
do you have a textbox named "txtFileName " in your form?

you can also try this codes
*you need to add Microsoft Office 14.0 object library to your references
* you also need textbox named "txtFileName "


Private Sub SelectSurvey_Click()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .Filters.Clear
    .Filters.Add "Microsoft Excel", "*.xls,*.xlsx"
    .Title = "Select Excel file"
    .InitialFileName = CurrentProject.Path & "\"
    .AllowMultiSelect = False
    
    If .Show Then
        Me.txtFileName = .SelectedItems(1)
    End If
    

End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougf1rAuthor Commented:
Indeed, I had forgotten to bring over the textbox named "txtFileName". I added that back in and it works now.

Thanks Rey!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.