Excel VBA FSO.copyfile Source

[breitnet]

Hello, the current code below copies files from the source folder to the destination folder based on cell value and file extension. The files may or may not be used all the time, if the file is not in the source folder I am getting an error. I would like to add code that will skip to the next "phase" if not file is found. Thanks for any help.

Private Sub CommandButton2_Click()
'copy files into folder
    Dim FSO As Object
    Dim sourcePath As String
    Dim DestinationPath As String
    Dim fileExtn As String

'Phase_0
sourcePath = Sheet1.Cells(121, 9).Value
DestinationPath = Sheet1.Cells(110, 13).Value

Sheet1.Cells(115, 4).Value = Sheet1.Cells(100, 4).Value & Sheet1.Cells(110, 4).Value

fileExtn = Sheet1.Cells(115, 4).Value & "*.mpf"

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

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.folderExists(sourcePath) = False Then
    MsgBox sourcePath & "does not exist"
    Exit Sub
End If

If FSO.folderExists(DestinationPath) = False Then
    MsgBox DestinationPath & "does not exist"
    Exit Sub
End If

FSO.copyfile Source:=sourcePath & fileExtn, Destination:=DestinationPath

'Phase_1
sourcePath = Sheet1.Cells(121, 9).Value
DestinationPath = Sheet1.Cells(111, 13).Value

Sheet1.Cells(116, 4).Value = Sheet1.Cells(100, 4).Value & Sheet1.Cells(111, 4).Value

fileExtn = Sheet1.Cells(116, 4).Value & "*.mpf"

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

Set FSO = CreateObject("scripting.filesystemobject")
If FSO.folderExists(sourcePath) = False Then
    MsgBox sourcePath & "does not exist"
    Exit Sub
End If

If FSO.folderExists(DestinationPath) = False Then
    MsgBox DestinationPath & "does not exist"
    Exit Sub
End If

FSO.copyfile Source:=sourcePath & fileExtn, Destination:=DestinationPath

 

[NdNoviceHlp]

This should help U figure it out. Dave

MsgBox "SourcePath " & SourcePath & fileExtn & "  Destination " & DestinationPath
If FSO.fileexists(SourcePath & fileExtn) Then
FSO.copyfile Source:=SourcePath & fileExtn, Destination:=DestinationPath
End If

 

[breitnet]

I inserted this code and cannot get it to copy the file now. When I remove the If statement it will copy from the source folder to the destination as before.

 

[NdNoviceHlp]

It depends what source file is (what U have in your sheet). That's what the msgbox was for. The "SourcePath & fileExtn" must equal the whole file path of the source file. Dave

 

[breitnet]

fileExtn = Sheet1.Cells(115, 4).Value & "*.mpf"

The value in the sheet1 is only the first 4 characters of the file name. Example file name: 7UL1_A_10_WKA_200TT_AA.mpf, 7UL2_A_10_WKA_200TT_AA.mpf

I am only concerned with the first 4 characters and the .mpf extension nothing in between. Thanks

 

[NdNoviceHlp]

U need the entire path of the source file including drive directory and folder(s). The entire file name is also required. Where is this located....

7UL1_A_10_WKA_200TT_AA.mpf

Dave

 

[breitnet]

Perhaps there is another way to do this? Is it possible to use a wild card? I don't want to look at the entire path because there are many variations, the only constant are the first four characters.

 

[NdNoviceHlp]

The file path comes before the variations. U may have several folders involved but they all have to be somewhere. U have to have the entire file path in order to copy it somewhere. Dave