Error when trying to import in VBA

[fhurman]

Hello,

I have used some code that I found on this site to help a user import files
using pop up browse windoes as oposed to manually. In the end what I want to
do is add a primary key column automatically once tehy import the file and
have this done seemlessly to the user. I'm having trouble using an Input Box
when importing the file, I'm geting error 2498 about the expression being the
wrong data type. I'm sure I'm overlooking something very easy here. Here's
my current code:

Private Sub CmdImport_Click()
DoCmd.SetWarnings True
'calls on the vbOpenFile Module
Dim strFilter As String
Dim strInputFileName As String
Dim FormatFileName As String

strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

If strInputFileName = "" Then End

'Actually Import Spreadsheet
FormatFileName = InputBox("Please enter the formatted File Name for Access")

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, FormatFileName, , strInputFileName, True


MsgBox "File Imported Successfully!", , "Status"

End Sub

My goal is to us that FormatFileName variable after the Docmd
transferspreadsheet and use an Alter Table expression to add the Primary Key
to the table name contained in the FormatFileName variable.

Any Help? TIA

Mark

 

[Douglas J. Steele]

I suspect your problem is the fact that you have two commas between
FormatFileName and strInputFileName.

The fourth argument (which is currently that space between the two commas)
is supposed to be the file name, and the fifth argument (which is currently
strInputFileName) is supposed to be a boolean value indicating whether or
not the first row of the spreadsheet contains the field names.