VBA code error

Posted on 2011-10-26
Last Modified: 2012-05-12
Trying to let user select a file to import. The code goes to the predetermined area but when a file is selected, nothing happens. The selection is not recognized.

Private Sub NewImportSelected_Click()
Dim strfilter As String
Dim strSaveFilename As String
strfilter = ahtAddFilterItem("Excel Files (*.xls)", "*.xls")
strSaveFilename = ahtCommonFileOpenSave(OpenFile:=True, Filter:=strfilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY) 'set OpenFile to true if selecting a file to open
DoCmd.SetWarnings False
If strSaveFilename <> "" Then
    DoCmd.TransferSpreadsheet acImport, 8, "Open Items", strSaveFilename, True, "Detail!A12:T40000"
    'DoCmd.DeleteObject acTable, "_ImportErrors"
    MsgBox "                     Import Complete                                "
End If
DoCmd.SetWarnings True

End Sub
Question by:eyes59
    LVL 119

    Accepted Solution

    where are the rest of the codes ?

    try using the sample db from here
    LVL 119

    Expert Comment

    by:Rey Obrero
    try this one

    LVL 31

    Expert Comment

    It might be helpful to put in a Debug.Print line to display the value stored in strSaveFilename, to see if it is correct.  Also, step through the code to see which line triggers the error.  It might be with the TransferSpreadsheet statement.
    LVL 31

    Expert Comment

    I like to use named arguments, and save all values to variables before using them to set the arguments, for greater clarity, as in this sample from my new Working with Excel ebook:
    DoCmd.TransferSpreadsheet transfertype:=acImport, _
       spreadsheettype:=acSpreadsheetTypeExcel9, _
       tablename:=strTable, _
       FileName:=strXLFile, _
       hasfieldnames:=True, _

    Open in new window

    LVL 31

    Expert Comment

    This method lets you examine each variable in the Immediate window, using Debug.Print statements, or the Watch window, in case there is a problem.

    Author Closing Comment

    The link to this was extreemly helpful:

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    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 use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 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