Error trapping empty directory 1

[jon92]

I am trying to error trap for an empty directory when importing a text file, the following code produces: runtime error 3001 Invalid argument, ( the transfer text line is highlighted) Where am I going wrong please?


Function testing() As Long
Dim strFile As String

strFile = "e:\mydir\" & Dir("e:\mydir\*.*&quot

IF strFile = "" Then
msgBox "No file found"
Exit Function
End if

DoCmd.TransferText acImportDelim, "myspec", "mytbl", strFile
End Function

 

[RickSpr]

Are you sure that your problem is that the directory is empty? This code excerpt should display the message box and exit as you intended. What is the value of strFile when the error occurs?

The Invalid Argument error could be arising because strFile isn't compatible with the import spec "myspec". It might, for example, not be a CSV file, or it might have field names on the first line (you omitted the parameter for field names, and it defaults to False).

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JeremyNYC]

Second time in five minutes that I'll follow Rick's good insight with a very minor point <g>.

It won't affect your code in any way other than making it easier to read, but I would redo this code like this:
Function testing() As Long
Dim strFile As String

strFile = &quot;e:\mydir\&quot; & Dir(&quot;e:\mydir\*.*&quot
If strFile <> &quot;&quot; Then
DoCmd.TransferText acImportDelim, &quot;myspec&quot;, &quot;mytbl&quot;, strFile
end if
End Function

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[RickSpr]

Oops! Jeremy, after the assignment statement, strFile will never be an empty string. Perhaps you meant:
If strFile <> &quot;e:\mydir\&quot; Then

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jon92]

RickSpr
I have tested the import spec and that is ok - the value of strFile when error occurs is: strFile = &quot;e:\mydir\&quot;

JeremyNYC I have tried your example and it generates the same error message.

What I am trying to do, is warn users that a file is missing and check their previous actions. If it helps there will only ever be 1 file in the directory during the import.

thankyou both

 

[jon92]

I have tried a different approach and this is working!
But I'm not sure why??

Function testing() As Long
Dim strFile As String, strPath As String
strPath = &quot;e:\mydir\&quot;
strFile = Dir(strPath & &quot;*.*&quot

If strFile = &quot;&quot; Then

MsgBox &quot;No File Found&quot;
Exit Function
Else
MsgBox &quot;You have 1 File&quot;

End If

DoCmd.TransferText acImportDelim, &quot;myspec&quot;, &quot;mytbl&quot;, strFile

End Function

 

[RickSpr]

Oh! Somehow I overlooked the problem in the original post, but when Jeremy repeated it I caught it. The problem was with the statement:
strFile = &quot;e:\mydir\&quot; & Dir(&quot;e:\mydir\*.*&quot
If there are no files in the directory, the Dir() part will return an empty string. But the empty string will be concatenated to &quot;e:\mydir\&quot; and then assigned to strFile, so strFile will never be an empty string and you'll never show the MsgBox.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jon92]

Rick

Thank you for the explanation - It's all becoming clearer now

Jon