Vba- copying PDF from subfolders to a new folder

Sarge24

New Member
Joined
Jan 21, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I am having a hard time with my vba code. I’m trying to move some specific PDF Files that are in subfolders to a new folder but the code it’s copying every PDF files from those subfolders. Here is the code:


VBA Code:
Sub CopyFiles_DEBUG()

    Dim sPathSource As String, sPathDest As String, sFileSpec As String
   
   
    Dim xRg As Range, xCell As Range

    Dim xSFileDlg As FileDialog, xDFileDlg As FileDialog

    Dim xSPathStr As Variant, xDPathStr As Variant

    Dim xVal As String
   
    Dim fso As Object, folder1 As Object

    On Error Resume Next

    Set xRg = Application.InputBox("Please select the file names:", "command", ActiveWindow.RangeSelection.Address, , , , , 8)

    If xRg Is Nothing Then Exit Sub

    sPathSource = "path to all folders and subfolders"
    sPathDest = "where the specific invoices should be copied"

    sFileSpec = "*.pdf"
   

    Call CopyFiles_FromFolderAndSubFolders_DEBUG(sFileSpec, sPathSource, sPathDest)
End Sub
   
Sub CopyFiles_FromFolderAndSubFolders_DEBUG(ByVal argFileSpec As String, ByVal argSourcePath As String, ByRef argDestinationPath As String)

    Dim sPathSource As String, sPathDest As String, sFileSpec As String

    Dim fso         As Object
    Dim oRoot       As Object
    Dim oFile       As Object
    Dim oFolder     As Object

    sPathSource = argSourcePath
    sPathDest = argDestinationPath

    If Not Right(sPathDest, 1) = "\" Then sPathDest = sPathDest & "\"
    If Right(sPathSource, 1) = "\" Then sPathSource = Left(sPathSource, Len(sPathSource) - 1)

    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FolderExists(sPathSource) And fso.FolderExists(sPathDest) Then
        Set oRoot = fso.GetFolder(sPathSource)
Debug.Print "PROCESSING >> " & oRoot.Path
        For Each oFile In oRoot.Files
            If LCase(oFile.Name) Like argFileSpec Then
                On Error Resume Next
                oFile.Copy sPathDest & oFile.Name
If Err.Number = 0 Then
Debug.Print "COPIED : " & oFile.Name
Else
Debug.Print "error  : " & oFile.Name
End If
                On Error GoTo 0
Else
Debug.Print "skipped: " & oFile.Name
            End If
        Next oFile
        For Each oFolder In oRoot.SubFolders
            Call CopyFiles_FromFolderAndSubFolders_DEBUG(argFileSpec, oFolder.Path, sPathDest)
        Next oFolder
    End If
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Welcome to MrExcel forums.

I’m trying to move some specific PDF Files that are in subfolders to a new folder
Your code allows the user to select a range of cells (containing the file names, I assume), but doesn't pass the selected range to the copying procedure, passing "*.pdf" instead.

This macro assumes the file names in the selected cells are in the format "file.ext", eg. "1234.pdf". Also note that if the same file occurs in multiple subfolders, the file is copied from each of the subfolders, resulting in the destination folder containing the last file that was copied.

VBA Code:
Public Sub CopyFiles_DEBUG()

    Dim sPathSource As String, sPathDest As String
    Dim filesRange As Range

    On Error Resume Next
    Set filesRange = Application.InputBox("Please select the file names:", "Copy Files", ActiveWindow.RangeSelection.Address, , , , , 8)
    If filesRange Is Nothing Then Exit Sub

    sPathSource = "C:\Path\to\main folder"        '"path to all folders and subfolders"
    sPathDest = "C:\Path\to\destination folder"   '"where the specific invoices should be copied"

    CopyFiles_FromFolderAndSubFolders_DEBUG filesRange, sPathSource, sPathDest
    
End Sub
   
   
Private Sub CopyFiles_FromFolderAndSubFolders_DEBUG(argFilesRange As Range, ByVal argSourcePath As String, ByRef argDestinationPath As String)

    Static FSO As Object
    Dim oFile As Object
    Dim oFolder As Object
    Dim fileCell As Range

    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    Debug.Print "SOURCE FOLDER " & argSourcePath
    Set oFolder = FSO.GetFolder(argSourcePath)
    
    For Each fileCell In argFilesRange
        If FSO.FileExists(oFolder.Path & "\" & fileCell.Value) Then
            Debug.Print "COPY " & oFolder.Path & "\" & fileCell.Value & " TO " & argDestinationPath
            FSO.CopyFile oFolder.Path & "\" & fileCell.Value, argDestinationPath, OverWriteFiles:=True
        End If
    Next
        
    For Each oFolder In oFolder.SubFolders
        CopyFiles_FromFolderAndSubFolders_DEBUG argFilesRange, oFolder.Path, argDestinationPath
    Next

End Sub
 
Upvote 0
Welcome to MrExcel forums.


Your code allows the user to select a range of cells (containing the file names, I assume), but doesn't pass the selected range to the copying procedure, passing "*.pdf" instead.

This macro assumes the file names in the selected cells are in the format "file.ext", eg. "1234.pdf". Also note that if the same file occurs in multiple subfolders, the file is copied from each of the subfolders, resulting in the destination folder containing the last file that was copied.

VBA Code:
Public Sub CopyFiles_DEBUG()

    Dim sPathSource As String, sPathDest As String
    Dim filesRange As Range

    On Error Resume Next
    Set filesRange = Application.InputBox("Please select the file names:", "Copy Files", ActiveWindow.RangeSelection.Address, , , , , 8)
    If filesRange Is Nothing Then Exit Sub

    sPathSource = "C:\Path\to\main folder"        '"path to all folders and subfolders"
    sPathDest = "C:\Path\to\destination folder"   '"where the specific invoices should be copied"

    CopyFiles_FromFolderAndSubFolders_DEBUG filesRange, sPathSource, sPathDest
   
End Sub
  
  
Private Sub CopyFiles_FromFolderAndSubFolders_DEBUG(argFilesRange As Range, ByVal argSourcePath As String, ByRef argDestinationPath As String)

    Static FSO As Object
    Dim oFile As Object
    Dim oFolder As Object
    Dim fileCell As Range

    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    Debug.Print "SOURCE FOLDER " & argSourcePath
    Set oFolder = FSO.GetFolder(argSourcePath)
   
    For Each fileCell In argFilesRange
        If FSO.FileExists(oFolder.Path & "\" & fileCell.Value) Then
            Debug.Print "COPY " & oFolder.Path & "\" & fileCell.Value & " TO " & argDestinationPath
            FSO.CopyFile oFolder.Path & "\" & fileCell.Value, argDestinationPath, OverWriteFiles:=True
        End If
    Next
       
    For Each oFolder In oFolder.SubFolders
        CopyFiles_FromFolderAndSubFolders_DEBUG argFilesRange, oFolder.Path, argDestinationPath
    Next

End Sub
So with this input I can search the exactly PDF I want but it doesn’t copy the files. The destinationpath it’s empty at the end of the process.
 
Upvote 0
Do the cells you select contain file names in the format "file.ext"? Is the sPathSource string correct?

Add this line immediately before the IF FSO.FileExists line and confirm that it is looking for the correct files.
VBA Code:
Debug.Print oFolder.Path & "\" & fileCell.Value
 
Upvote 0
Welcome to MrExcel forums.


Your code allows the user to select a range of cells (containing the file names, I assume), but doesn't pass the selected range to the copying procedure, passing "*.pdf" instead.

This macro assumes the file names in the selected cells are in the format "file.ext", eg. "1234.pdf". Also note that if the same file occurs in multiple subfolders, the file is copied from each of the subfolders, resulting in the destination folder containing the last file that was copied.

VBA Code:
Public Sub CopyFiles_DEBUG()

    Dim sPathSource As String, sPathDest As String
    Dim filesRange As Range

    On Error Resume Next
    Set filesRange = Application.InputBox("Please select the file names:", "Copy Files", ActiveWindow.RangeSelection.Address, , , , , 8)
    If filesRange Is Nothing Then Exit Sub

    sPathSource = "C:\Path\to\main folder"        '"path to all folders and subfolders"
    sPathDest = "C:\Path\to\destination folder"   '"where the specific invoices should be copied"

    CopyFiles_FromFolderAndSubFolders_DEBUG filesRange, sPathSource, sPathDest
   
End Sub
  
  
Private Sub CopyFiles_FromFolderAndSubFolders_DEBUG(argFilesRange As Range, ByVal argSourcePath As String, ByRef argDestinationPath As String)

    Static FSO As Object
    Dim oFile As Object
    Dim oFolder As Object
    Dim fileCell As Range

    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    Debug.Print "SOURCE FOLDER " & argSourcePath
    Set oFolder = FSO.GetFolder(argSourcePath)
   
    For Each fileCell In argFilesRange
        If FSO.FileExists(oFolder.Path & "\" & fileCell.Value) Then
            Debug.Print "COPY " & oFolder.Path & "\" & fileCell.Value & " TO " & argDestinationPath
            FSO.CopyFile oFolder.Path & "\" & fileCell.Value, argDestinationPath, OverWriteFiles:=True
        End If
    Next
       
    For Each oFolder In oFolder.SubFolders
        CopyFiles_FromFolderAndSubFolders_DEBUG argFilesRange, oFolder.Path, argDestinationPath
    Next

End Sub
This works perfectly for me but the cells I am selecting only contain part of the file name and doesn't contain the .pdf extention. How could I amend this coding to allow for partial match on the cell references?

Thanks,

Tom
 
Upvote 0
This works perfectly for me but the cells I am selecting only contain part of the file name and doesn't contain the .pdf extention. How could I amend this coding to allow for partial match on the cell references?
This macro should do it.
VBA Code:
Option Explicit

Public Sub CopyFiles_Partial_File_Names()

    Dim sourcePath As String, destinationPath As String
    Dim filesRange As Range

    sourcePath = "C:\path\to\folder"  'main folder and its subfolders to search for the partial file names
    destinationPath = "C:\destination\folder\" 'folder where matching file names will be copied to

    On Error Resume Next
    Set filesRange = Application.InputBox("Please select the cells containing partial file names to be copied:", "Copy Files", ActiveWindow.RangeSelection.Address, , , , , 8)
    On Error GoTo 0
    If filesRange Is Nothing Then Exit Sub

    Copy_Matching_PDF_Files filesRange, sourcePath, destinationPath
    
End Sub
   
   
Private Sub Copy_Matching_PDF_Files(filesRange As Range, sourceFolder As String, ByVal destinationFolder As String)

    Static FSO As Object
    Dim FSfile As Object
    Dim FSfolder As Object
    Dim fileCell As Range

    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"

    Set FSfolder = FSO.GetFolder(sourceFolder)
    
    For Each fileCell In filesRange
        For Each FSfile In FSfolder.files
            If LCase(FSfile.Name) Like LCase("*" & fileCell.Value & "*.pdf") Then
                Debug.Print "COPY " & FSfile.Path & " TO " & destinationFolder
                FSfile.Copy destinationFolder, OverwriteFiles:=True
            End If
        Next
    Next
        
    For Each FSfolder In FSfolder.SubFolders
        Copy_Matching_PDF_Files filesRange, FSfolder.Path, destinationFolder
    Next

End Sub
 
Upvote 0
This macro should do it.
VBA Code:
Option Explicit

Public Sub CopyFiles_Partial_File_Names()

    Dim sourcePath As String, destinationPath As String
    Dim filesRange As Range

    sourcePath = "C:\path\to\folder"  'main folder and its subfolders to search for the partial file names
    destinationPath = "C:\destination\folder\" 'folder where matching file names will be copied to

    On Error Resume Next
    Set filesRange = Application.InputBox("Please select the cells containing partial file names to be copied:", "Copy Files", ActiveWindow.RangeSelection.Address, , , , , 8)
    On Error GoTo 0
    If filesRange Is Nothing Then Exit Sub

    Copy_Matching_PDF_Files filesRange, sourcePath, destinationPath
   
End Sub
  
  
Private Sub Copy_Matching_PDF_Files(filesRange As Range, sourceFolder As String, ByVal destinationFolder As String)

    Static FSO As Object
    Dim FSfile As Object
    Dim FSfolder As Object
    Dim fileCell As Range

    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"

    Set FSfolder = FSO.GetFolder(sourceFolder)
   
    For Each fileCell In filesRange
        For Each FSfile In FSfolder.files
            If LCase(FSfile.Name) Like LCase("*" & fileCell.Value & "*.pdf") Then
                Debug.Print "COPY " & FSfile.Path & " TO " & destinationFolder
                FSfile.Copy destinationFolder, OverwriteFiles:=True
            End If
        Next
    Next
       
    For Each FSfolder In FSfolder.SubFolders
        Copy_Matching_PDF_Files filesRange, FSfolder.Path, destinationFolder
    Next

End Sub


@John_w , your code is wonderful, it really helped me, but i am struggling to modify to satisfy one more need, could you help me pointing me to the right direction?

1614565541223.png


I intend to use this to make some "quick-send" laser cutting material based on a excel list, so there are some factors to consider,

Red = the codes the program will search;
Blue = the materials that it will use to create specific folders;
Yellow = from left to right, equipament model, my internal system code and revision of design;

Based on that information, it will create some folders like that,

1614565652778.png


folder name = Model + Material + Internal system code + Revision

These folders will be inside a destination folder like it's working now. But the program needs to check what materials are in the list, create the correct folders and them put each code according to their material in this folder. If the folder already has created, then it doesn't create again.

I modified the code to supply the ".dxf" file and also created the variables for each material and also the new range for comparison purposes


VBA Code:
Option Explicit

Public Sub CopyFiles_Partial_File_Names()

    Dim sourcePath As String, destinationPath As String
    Dim filesRange As Range

    sourcePath = "C:\Users\Avell\Google Drive LTCH\SOLIDWORKS"  'main folder and its subfolders to search for the partial file names
    destinationPath = Application.InputBox("Input destination folder to all files:", , , , , , , 2) 'folder where matching file names will be copied to

    On Error Resume Next
    Set filesRange = Application.InputBox("Please select the cells containing partial file names to be copied:", "Copy Files", ActiveWindow.RangeSelection.Address, , , , , 8)
    On Error GoTo 0
    If filesRange Is Nothing Then Exit Sub

    Copy_Matching_PDF_Files filesRange, sourcePath, destinationPath
    
End Sub
   
   
Private Sub Copy_Matching_PDF_Files(filesRange As Range, sourceFolder As String, ByVal destinationFolder As String)

    Static FSO As Object
    Dim FSfile As Object
    Dim FSfolder As Object
    Dim fileCell As Range
    Dim model As String
    Dim revision As String
    Dim code As String
    Dim ac120 As String
    Dim ac200 As String
    Dim ac318 As String
    Dim ai120 As String
    Dim folderac120 As String
    Dim folderac200 As String
    Dim folderac318 As String
    Dim folderai120 As String
    Dim fileMaterial As Range
    
    Set model = Worksheets(ActiveSheet.Name).Cells(1, "A").Value
    Set revision = Worksheets(ActiveSheet.Name).Cells(1, "C").Value
    Set code = Worksheets(ActiveSheet.Name).Cells(1, "B").Value
    Set ac120 = "CHAPA AÇO 1020 1,20MM"
    Set ac200 = "CHAPA AÇO 1020 2,00MM"
    Set ac318 = "CHAPA AÇO 1020 3,18MM"
    Set ai120 = "CHAPA INOX 304 1,2MM ESCOVADO COM PELICULA"
    Set folderac120 = "1,20mm"
    Set folderac200 = "2,00mm"
    Set folderac318 = "3,18mm"
    Set folderai120 = "INOX 304 1,20mm"
    Set fileMaterial = filesRange.Offset(, 2)

    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"

    Set FSfolder = FSO.GetFolder(sourceFolder)
    
    For Each fileCell In filesRange
        For Each FSfile In FSfolder.Files
            If LCase(FSfile.Name) Like LCase("*" & fileCell.Value & "*.pdf") Then
                Debug.Print "COPY " & FSfile.Path & " TO " & destinationFolder
                FSfile.Copy destinationFolder, OverwriteFiles:=True
            End If
        Next
    Next
    
        For Each fileCell In filesRange
        For Each FSfile In FSfolder.Files
            If LCase(FSfile.Name) Like LCase("*" & fileCell.Value & "*.dxf") Then
                Debug.Print "COPY " & FSfile.Path & " TO " & destinationFolder
                FSfile.Copy destinationFolder, OverwriteFiles:=True
            End If
        Next
    Next
        
    For Each FSfolder In FSfolder.SubFolders
        Copy_Matching_PDF_Files filesRange, FSfolder.Path, destinationFolder
    Next

End Sub

Basic modifying.

Could you enlight me on how to make this modification?
 
Upvote 0
Your request seems very different to the OP's, so I suggest you start a new thread or ask a moderator to move your post to a new thread.
 
Upvote 0

Forum statistics

Threads
1,223,317
Messages
6,171,419
Members
452,402
Latest member
siduslevis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top