Need help with importing files 1

[PROXI]

Hello everyone,

I am trying to get some code to work and am not having much luck. I keep getting a Jet error 3011 saying that it can't find the file "ppab0379.4837.txt". I know that the file exists just as it is above. Any help would be appreciated.

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Dim strImportFile As String, strTableName As String
Dim strfile As String
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFileExt = ".txt"
strTableName = "TblDailyFiles"
strImportFile = Dir(strFolderPath & "\*" & strFileExt)
strfile = strFolderPath & strImportFile

strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Daily Recon\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
    DoCmd.TransferText acImportDelim, "TxtImportSpec", strTableName, strfile
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

[Code]

Thanks,

PROXI

 

[xwordmark]

It appears you have referenced (twice) the variable strFolderPath before you set its value. You need to move the following line of code up two lines:

strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Daily Recon\"

Also, as you have the backslash at the end of the variable strFolderPath, you do not need to include it in the middle of the Dir function

 

[PROXI]

I did the changes that you suggested and it gave me another error:

"3001 Invalid Argument"

If I leave the & "\*" & in the line below, I still receive the Jet error 3011 saying that it can't find the file "ppab0379.4837.txt":

strImportFile = Dir(strFolderPath & "\*" & strFileExt)


Thanks,

PROXI

 

[PROXI]

I had also tried this code to do the same thing and couldn't get the loop to work right. If I can get either of them to work right I would be happy. See if this one is any simpler to fix

Private Sub getfiles_Click()
Dim db As DAO.Database
Dim strInputDir, strImportFile As String, strTableName As String
Dim strFileExt As String
Dim strfile As String

Call ChangeFilename

' the pathname of the folder that contains the files you want to import."
strInputDir = "C:\Documents and Settings\parkes23\My Documents\Daily Recon\"

' the file extension for the type of file you want to import.
strFileExt = ".txt"

' the destination table in the database
strTableName = "TblTempDailyFiles"

strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb

strfile = strInputDir & strImportFile

For Each varfile In strInputDir
    DoCmd.TransferText acImportDelim, "TxtImportSpec", strTableName, strfile
    Next
End
End Sub

[Code]

Thanks,

PROXI

 

[xwordmark]

Well, I'm not certain because I've never worked with a file that had a dot in the filename other than right before the extension, but that might be what's giving you the problem. Can you rename the file and change that dot to an underscore (or whatever works for you) and try it again?

 

[PROXI]

That worked... You are the MAN!!!!!

Thanks,

PROXI

 

[PROXI]

ok... next problem.

The code isn't looping through correctly. I have added a new part to the code so that once the file is imported, the file is copied to another directory and then deleted. Now I get an error 53 file not found. Best that I can tell, it isn't finding the next file in the directory. I have about 4 files currently in there that it should be looping through, but isn't. This is the updated code that I have as of right now:

[Code}

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Dim strImportFile As String, strTableName As String
Dim strfile As String
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim strnew As String


strFileExt = ".txt"
strTableName = "TblTempDailyFiles"
strFolderPath = "C:\Documents and Settings\parkes23\My Documents\Daily Recon\"
strImportFile = Dir(strFolderPath & "\*" & strFileExt)
strfile = strFolderPath & strImportFile
strnew = "C:\Documents and Settings\parkes23\My Documents\Daily Recon\"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportFixed, "TxtImportSpec", strTableName, strfile
End If
Name strfile As "C:\Documents and Settings\parkes23\My Documents\Daily Recon\completed\" & strImportFile

Kill strfile

Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

Thanks,

PROXI

 

[xwordmark]

Actually, I think its bombing at the 'Kill strfile' because the Name statement not only gives the file a new name, it moves it so that it doesn't exist in its original location.

Since strfile has moved, the Kill function can't find strfile, and there is no need for the Kill function anyway.

 

[PROXI]

I took that out and now I am getting the 3011 jet error. The error is saying the the database is trying to look for the file that was just moved to the completed directory. It seems like the strfile variable isn't updating to the next file in the list.

Thanks,

PROXI

 

[xwordmark]

PROXI,

Sorry I couldn't finish with you yesterday. Anyway, change the following section and you should be good to go.

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
strfile = objF1
DoCmd.TransferText acImportFixed, "TxtImportSpec", strTableName, strfile
Name strfile As strnew & objF1.Name
End If
Next