Solved

Select Excel File for Import with Form Button

Posted on 2014-11-04
4
1,252 Views
Last Modified: 2014-11-04
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.
0
Comment
Question by:dougf1r
4 Comments
 
LVL 84
ID: 40422993
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
 

Expert Comment

by:gracie1972
ID: 40423003
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40423013
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
 
LVL 1

Author Comment

by:dougf1r
ID: 40423045
Indeed, I had forgotten to bring over the textbox named "txtFileName". I added that back in and it works now.

Thanks Rey!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question