Moving PDF files from one folder to another

[Chris_Li]

Hi Team,

following scenario:

I have a source folder, containing PDF files. Location would look like: Z:\aaaa\bbb\ccccc\2022\ddddddd\eeeeeee\ffffff\this_is_the_folder_containing_all_the_pdfs\*.pdf

The naming convention for the pdf files, is Account number_Invoice number: 123456_654321.pdf

A macro is already creating folders, named for the account number. Here in that example: A folder with name 123456 would exist.

The location for that folder would look like this:
Z:\aaaa\bbb\ccccc\2022\ddddddd\eeeeeee\ffffff\this_is_the_folder_with_the_account_folders\123456\

I tried to find a solution to move the pdf files, based on the part of their account number to their respective folders.

I have found a piece of code I thought it would be suitable:


Sub MoveFiles()

Dim objFSO As Object
Dim objMyFolder As Object
Dim objMyFile As Object
Dim strMyFolder As String

Application.ScreenUpdating = False

strMyFolder = "Z:\aaaa\bbb\ccccc\2022\ddddddd\eeeeeee\ffffff\this_is_the_folder_containing_all_the_pdfs" '<--Directory path where the relevant PDF files are.


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objMyFolder = objFSO.GetFolder(strMyFolder)

For Each objMyFile In objMyFolder.Files
'If the current file's extension is 'pdf' and it has a underscore in its name, then...
If objFSO.GetExtensionName(objMyFile) = "pdf" And InStr(objMyFile.Name, "_") > 0 Then
'check if the directory exists. If it doesn't then...
If objFSO.FolderExists(strMyFolder & "\" & Left(objMyFile.Name, WorksheetFunction.Search("_", objMyFile.Name) - 1)) = False Then
'...inform the user.
MsgBox "Cannot move the file"
'Else...
Else
'...move the pdf to the folder
objFSO.MoveFile (strMyFolder & "\" & objMyFile.Name), strMyFolder & "\" & Left(objMyFile.Name, WorksheetFunction.Search("_", objMyFile.Name) - 1)
End If
End If
Next objMyFile

Set objFSO = Nothing
Set objMyFolder = Nothing

Application.ScreenUpdating = True

End Sub


But it throws the Message box that it "Cannot move the file" and I think that is based on the fact that source and and location of the file are not the same directory.

How can I implement this information in above code or is there even a better solution?

Thank you all for your time!

 

[offthelip]

You can "move" files using the Name function in vba . I uise the subroutine for moving files, after I have generated the list of files on my "tobemoved" spreadsheet:
You could modify this for your purposes:

Sub movefile()
' this subroutine move the files to the folder this workbook is in
Dim pathn As String
Dim src As String
Dim dst As String

pathn = ActiveWorkbook.Path
Worksheets("tobemoved").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 10))
For i = 2 To lastrow
'You can change the path and file name
    src = inarr(i, 2)
    dst = pathn & "\" & inarr(i, 1)
     Name src As dst
     
Next i
End Sub

 

[John_w]

Try this, which creates the subfolders if they don't exist and moves the files.

Public Sub Move_Files_To_Subfolders()
    
    Dim matchFiles As String
    Dim fromFolder As String, toFolder As String
    Dim FSO As Object 'Scripting.FileSystemObject
    Dim FSfromFolder As Object 'Scripting.Folder
    Dim FSfile As Object 'Scripting.File
    
    matchFiles = "Z:\aaaa\bbb\ccccc\2022\ddddddd\eeeeeee\ffffff\this_is_the_folder_containing_all_the_pdfs\*_*.pdf"
    
    fromFolder = Left(matchFiles, InStrRev(matchFiles, "\"))
    
    Set FSO = New Scripting.FileSystemObject
    Set FSfromFolder = FSO.GetFolder(fromFolder)
    For Each FSfile In FSfromFolder.Files
        If LCase(FSfile.Path) Like LCase(matchFiles) Then
            toFolder = fromFolder & Left(FSfile.Name, InStr(FSfile.Name, "_") - 1) & "\"
            If Not FSO.FolderExists(toFolder) Then FSO.CreateFolder toFolder
            Debug.Print "MOVE " & FSfile.Path, toFolder
            FSfile.Move toFolder
        End If
    Next

End Sub

 

[Chris_Li]

Hi offthelip and John_w, thank you very much for your time and the answers to a newb. Unfortunaltely I hqave to bother more...
@John_w, trying your code throws attached error: John_w_MoveFilesMacro. How can I define the type?
@offthelip, after I declared the variables LastRow, inarr and i and entering the worksheets to select, running that code then informes me off an expected array as you can see in the offthelip_moveFiles attachement.

Please don't hesitate if you have questions.
Every answer is always highly appreciated!

Thanks

Chris

 

[offthelip]

Inarr is a variant array. If you load a variable from a range in a spreadhseet the type has to be variant, this is because a cell could be text, numerical, logical, date, etc.
If you don't declare it , provided you don't have "option explicit" excel defaults to a variant which works fine. I never use option explicit because I find it far more trouble that assistance.
If I declare something it is usually because a variable realy needs to be that type rather than the default variant. i.e src, dst and pathn

 

[John_w]

@John_w, trying your code throws attached error: John_w_MoveFilesMacro. How can I define the type?

Sorry, I missed changing that bit to use late binding of the FileSystemObject. Change the highlighted line to:

    Set FSO = CreateObject("Scripting.FileSystemObject")