Solved

Select Excel File for Import with Form Button

Posted on 2014-11-04
4
1,190 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 119

Accepted Solution

by:
Rey Obrero 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now