VBA: Moving files to different folders

Ziri · Mar 19, 2020

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?

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

ranman256 · Mar 19, 2020

Code:

For i = 1 To 100000
vFileNam = Range("A" & i)
vSrcDir = Range("O" & i)
vTargDir = Range("P" & i)

vSrcFile = vSrcDir & vFileNam
vTargFile = vTargDir & vFileNam

'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"

If InStr(vFileNam, ".pdf") > 0 Then
    Name vSrcFile As vTargFile
End If
Next

Ziri · Mar 20, 2020

Thank you for the code. I tried to changed it but it still gives mi the same error.

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
vFileNam = Range("A" & i)
vSrcDir = Range("O" & i)
vTargDir = Range("P" & i)
vSrcFile = vSrcDir & vFileNam
vTargFile = vTargDir & vFileNam
'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"
If InStr(vFileNam, ".pdf") > 0 Then
Name vSrcFile As vTargFile
End If
Next
'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 vSrcFile, vTargFile
End Sub

LazyBug · Mar 22, 2020

Hello!
Can you post the sample data with XL2BB please?

Ziri · Mar 22, 2020

Hello,

here are some sample data. Thank you!

makro.xlsm

A

B

C

D

1

File

File wt ext

Destination Folder

Source Folder

2

196585

196585.pdf

Z:\Ucetnictvi\Capexus\FAKTURY PŘIJATÉ\STAV NOUZE\Makro\Doklady zaplacené\