VBA Macro to search a PDF file and copy to the folder-CodePudding

In a excel table, I have 10 file names like:

test1, test2, test3

All of the file are pdfs. Then I need to search for those files in a folder. If I found test1 at path C:\Users\SOURCE then I take this and copy to my desktop folder C:\Users\Destination.

But nothing happens, it didn't copy any files from source -> destination folder.

Sub copyFile()
  Dim objFSO As Object
  Dim strFileToCopy, strOldPath As String, strNewPath As String
  
  strOldPath = "C:\Users\SOURCE" 'Verzeichnis in dem die Datei liegt
  strNewPath = "C:\Users\Destination" 'Verzeichnis in welches kopiert werden soll
  
  With ActiveSheet
    strFileToCopy = .Range("A1") 'Zelle mit dem Namen
    strFileToCopy = strFileToCopy & ".pdf" 'Suffix anhängen
    If Dir(strOldPath & strFileToCopy, vbNormal) <> "" Then
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      objFSO.copyFile strOldPath & strFileToCopy, strNewPath & strFileToCopy
    End If
  End With
  
  Set objFSO = Nothing
End Sub

I am very thankful for each help. Thanks a lot!

Additional information: the value in A1 is test1 the pdf file in the Source path is test1.pdf

CodePudding user response：

Don't concatenate path manually. Use FileSystemObject (Windows only) methods instead.

Dim objFSO As Object, OldPath As String
...
Set objFSO = CreateObject("Scripting.FileSystemObject")
OldPath = objFSO.BuildPath(strOldPath, strFileToCopy)
If objFSO.FileExists(OldPath) Then
  objFSO.copyFile OldPath, objFSO.BuildPath(strNewPath, strFileToCopy)
End If
...