File Path and Name in Import String

[Guest]

I've got a function that returns the file name and path desired by the user.
In the immediate window, the TestIt function returns the correct file path
with quotes around it. I can even take that file path from the Immediate
window and copy/paste it into the file name argument and it works perfectly

When I use the function inside this code, I get a Action or Method Requires
a File Name Argument error. Here's my code:

Function macImport()
On Error GoTo macImport_Err

DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acImport, 8, "Import", TestIt(), True, ""
MsgBox "Data import successful!", vbInformation, "Import Status"

macImport_Exit:
Exit Function

macImport_Err:
MsgBox Error$
Resume macImport_Exit

End Function

Does anyone spot any problems with this, particularly in relation to the
File Name Argument? I've included the TestIt () function code as well in
case that is causing a problem:

Function TestIt()

Dim strFilter As String
Dim strInputFileName As String

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

'Debug.Print strInputFileName

End Function

 

[Wayne Morgan]

In the TestIt() function, you don't assign a value to the function, so
nothing is returned.

Change the first line of the TestIt() function to:

Public Function TestIt() As String

Then where you're Debug.Print line is, add the following line:

TestIt = strInputFileName

 

[Guest]

In addition to Testit() returning no value, you will have a problem with the
TransferSpreadsheet if you return the value with the quotes around it. The
TransferSpreadsheet will be looking for a path and file that begins and ends
with quotes. You only need the quotes if you are not using a variable, but
passing a specific string.