How do I transfer .pdf files from one folder to another based of a condition in an excel file using a macro?

Question

To go into more detail:

I have an excel file containing a bunch of part numbers. I'd like to make a macro so that if a column is equal to "YES" then search the "Specs" folder for the specifications . pdf which matches the part number in the excel sheet and then copy the .pdf in that folder to the destination folder or "Dest" folder.

UPDATED CODE Trying to understand the Loop logic what I'm looking to go through

Sub Rectangle1_Click()

Dim ws As Worksheet
Dim lRow As Long, i As Long
Dim OldPath As String, NewPath As String

Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")



'~~> File location
OldPath = "C:\Users\bucklej\Desktop\Spec\"
NewPath = "C:\Users\bucklej\Desktop\Dest\"

'~~> This is the workbook from where the code is running
 Set ws = ThisWorkbook.Sheets("Specification Listing")

'~~> Loop through Col A
    For i = 1 To 1000
       Cells(i, 2).Value = Yes
    Next i

fso.CopyFile(OldPath, Newpath)

 End Sub

score 0 · Answer 1 · answered Sep 16 '14 at 14:55

0

No Need to use DIR and then splitting the file name. Here is a much faster way.

Try this (Tried And Tested)

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim OldPath As String, NewPath As String

    OldPath = "C:\Users\bucklej\Desktop\Test1\"
    NewPath = "C:\Users\bucklej\Desktop\Test2\"

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow

            '~~> If file doesn't exists then that line will be ignored
            On Error Resume Next
            LoopFile = .Range("A" & i).Value & ".txt"
            Name OldPath & LoopFile As NewPath & LoopFile
            LoopFile = .Range("A" & i).Value & ".docx"
            Name OldPath & LoopFile As NewPath & LoopFile
            LoopFile = .Range("A" & i).Value & ".xlsx"
            Name OldPath & LoopFile As NewPath & LoopFile
            LoopFile = .Range("A" & i).Value & ".xls"
            Name OldPath & LoopFile As NewPath & LoopFile
            LoopFile = .Range("A" & i).Value & ".bmp"
            Name OldPath & LoopFile As NewPath & LoopFile
            On Error GoTo 0

            DoEvents
        Next i
    End With
End Sub

answered Sep 16 '14 at 14:55

Siddharth Rout

147,039
17
206
250

`Maybe I worded this poorly.` Yes you did :) Please read your question. I answered what exactly you asked :D – Siddharth Rout Sep 16 '14 at 15:15
I am off to the gym. Should be back in 1.5 hours. I will re-visit the question then. – Siddharth Rout Sep 16 '14 at 15:16
I don't see you looping in your code to search for the part number? – Siddharth Rout Sep 16 '14 at 16:15
See this [example](http://stackoverflow.com/questions/25871114/excel-vba-find-and-replace-from-external-file) on how to loop through cells in a column. Simply combine that with the above code and it will work just fine. Give it a try and if you get stuck then post the code that you tried and then we will take it from there... – Siddharth Rout Sep 16 '14 at 16:49
2

josh, I can give you the code in a platter but I don't want to do that. It is not gonna help you. All you did was copy paste the code from there. I request you to understand how the for loop is working. – Siddharth Rout Sep 16 '14 at 17:05

How do I transfer .pdf files from one folder to another based of a condition in an excel file using a macro?

1 Answers1