Hello VBA Experts,
I have been trying to write a code for files (listed in column A) that I currently located in folder listed in column P to folders listed in column O. I have tried several approaches and have read many forums but without any results. Find below the last coda i tried but it is giving me "Invalide procedure call or argument" error. Could anyone push me the right direction please?
I have been trying to write a code for files (listed in column A) that I currently located in folder listed in column P to folders listed in column O. I have tried several approaches and have read many forums but without any results. Find below the last coda i tried but it is giving me "Invalide procedure call or argument" error. Could anyone push me the right direction please?
VBA Code:
Sub Move_Files_From_One_Folder_To_Another_Folder()
Dim FSO As Object
Dim FromDir As String
Dim ToDir As String
Dim FExtension As String
Dim FNames As String
Sheets("Data").Activate
Dim A As String
Dim B As String
Dim C As String
Dim i As Long
For i = 1 To 100000
A = Range("A" & i)
B = Range("O" & i)
C = Range("P" & i)
Next
FromDir = C & A
ToDir = B & A
'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
FExtension = "*.pdf"
'Now assign each file name with extension
FNames = Dir(FromDir & FExtension)
'Check whether there are any files in the folder so that you can exit if there are no files
If Len(FNames) = 0 Then
MsgBox ("No files in " & FromDir)
Exit Sub
End If
'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
Set FSO = CreateObject("Scripting.FileSystemObject")
'Now we move the file from the source directory to the destination directory
FSO.MoveFile Source:=FromDir & FExtension, Destination:=ToDir
End Sub