• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

VBA code error

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
  • 3
  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:
where are the rest of the codes ?

try using the sample db from here

Rey Obrero (Capricorn1)Commented:
try this one

Helen FeddemaCommented:
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Helen FeddemaCommented:
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

Helen FeddemaCommented:
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.
eyes59Author Commented:
The link to this was extreemly helpful:

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now