Loop for Importing and Moving Files

[Bonediggler1]

Hi,

I am writing a simple program that should loop through the files in a folder, import them and then move them to another folder.

I can get the import loop but do not quite know how to fit in the move part. Here's what I have so far...the code throws an error at the .foundfiles(i).move part.

Sub Blah_Import_and_Move()


'Determine folder and file to import
Dim strFileSource As String
Dim strFileName As String
strFileSource = "C:\Blah1\Blah2\Blah3\"
strFileName = "Blah *.*"

'Determine folder where to move files
Dim strMoveFolder As String
strMoveFolder = "c:\Blah4\Blah5\Blah6\"

'Import files and move to different folder after importing
Dim fs As Object
Set fs = Application.FileSearch
With fs
.lookin = strFileSource
.filename = "*.*"
If .Execute() > 0 Then
For i = 1 To .foundfiles.Count
DoCmd.TransferText acImportFixed, "Blah_Specs", "tblBlah_Raw", .foundfiles(1), False
.foundfiles(i).Move strMoveFolder
Next i
Else
GoTo ErrorHandler
End If
End With

End Sub


Thank you!!

 

[JerryKlmns]

Use the VBA Name statement that renames a file or moves it to a different folder

 

[Bonediggler1]

Hi JerryKlmns-

Thanks for the suggestion. I plugged in Name as follows:

Name .foundfiles(i) as strMoveFolder

and received a Syntax Error message. I'm guessing the Name Statement doesn't recognize .foundfiles(i) as a valid file name...?

 

[JerryKlmns]

I think you need to use it like

TheNameOfFileHere=Right(.foundfiles(i), Len(.foundfiles(i))-Len(strFileSource))
Name .foundfiles(i) As strMoveFolder & TheNameOfFileHere

 

[Bonediggler1]

I could be wrong, but I still don't think Name recognizes ".foundfiles(i)" because it is not a string.

Using the "item" property does seem to get around this as it translates .foundfiles(i) into a string. The below has worked so far:

'Identify the current file in the loop, with complete file path
strFileSourceA = .foundfiles.Item(i)

'Parse the file name only of the current file in the loop
strFileNameA = Right(strFileSourceA, 14)

'Move the file to the "Imported" folder
strMoveFolder = "c:\Blah1\Blah2\Blah3\" & strFileNameA
Name strFileSourceA As strMoveFolder

Thanks for helping me think it through~