Runtime error 5

  • Thread starter Thread starter lcannon1
  • Start date Start date
L

lcannon1

I am getting an run time error 5 "invalid procedure call" and
hightlights this line:

GetFileName = Left(GetFileName, InStr(1, GetFileName, ".") - 1)

when running the code below.


Function ImportFile() As Integer
' This function will return 1 if a file is imported, 0 if not imported
Dim FileSpec As String
Dim TableName As String
FileSpec = OpenTextFile("c:\download\")
TableName = GetFileName(TableName)
If Len(FileSpec) = 0 Then
MsgBox "Operation Cancelled - file not loaded", vbOKOnly, "ImportFile()
function"
ImportFile = 0
Else
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel3, TableName,
FileSpec, True
ImportFile = 1
End If
End Function
Function ParseDirectory(strFilePath As String) As String
ParseDirectory = Left(strFilePath, InStrRev(strFilePath, "\"))
End Function

Function GetFileName(strFile As String) As String
GetFileName = Mid(strFile, InStrRev(strFile, "\") + 1)
GetFileName = Left(GetFileName, InStr(1, GetFileName, ".") - 1)
End Function
 
GetFileName is a function, not a variable, so Left(GetFileName, ...)
attempts to call your function again. Try this:

Function GetFileName(strFile As String) As String
Dim strX As String
strX = Mid(strFile, InStrRev(strFile, "\") + 1)
GetFileName = Left(strX, InStr(1, strX, ".") - 1)
End Function


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top