Solved: VBA Move Files to Sub Folder

[gunny2k9]

With this code i need to find the best way to Move files to a sub folder ...


Dim xFile As Integer
Dim Filename As Variant

With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
End With

' Exit on Cancel
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If

' Open Files
For xFile = LBound(Filename) To UBound(Filename)
msg = msg & Filename(xFile) & vbCrLf ' This can be removed
Workbooks.Open Filename(xFile)
xName = Right(Filename(xFile), 14)
Call Interigate
Next xFile


I E once it has opened, processed and closed each document it needs to move said file/files to say a sub folder where the file is to like a Done folder#

any one have any ideas? would prefer a simple bit of code but any that works be good

[Kenneth Hobs]

Use SaveAs for the workbook if Interigate does not do it. Then use:
Kill xFile

You might want to use Dir(whateverpathSaveAs used) to see if the file was saved in the new folder before kill. If the Dir="" then the file does not exist so don't Kill the original.

[Bob Phillips]

Look at the Name Statement in VBA



Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

[gunny2k9]

Dim strfile As String
Dim strdest As String
Dim MOVER As Object

For xFile = LBound(Filename) To UBound(Filename)
'Get Current Directory Path from File and add \Complete
strdest = CurDir(Filename(xFile)) & "\Complete"
strfile = Dir(Filename(xFile))
Set MOVER = CreateObject("Scripting.FileSystemObject")
MOVER.MoveFile Filename(xFile), strdest & "\" & strfile ' Move Current File to new Folder
Set MOVER = Nothing
Next xFile


found a simplish solution thxs to a friend, just hope all code examples i put on this forum will come as usefull to others :thumb

[Bob Phillips]

Using Name, you don't need CreateObject



Dim strdest As String


For xFile = LBound(Filename) To UBound(Filename)

'Get Current Directory Path from File and add \Complete
strdest = CurDir(Filename(xFile)) & "\Complete"
Name Filename(xFile) As strdest & "\" & strfile ' Move Current File to new Folder
Next xFile

At the very least, you should create the FileSystemObject once, before the loop, and destroy it at the end, not every iteration of the loop.