asked on

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

try this one

FileBrowser-Import.mdb

Helen Feddema

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.

Helen Feddema

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, _
   Range:=strRange

Open in new window

Helen Feddema

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.

eyes59

ASKER

The link to this was extreemly helpful:
http://www.mvps.org/access/api/api0001.htm