Excel VBA FSO.copyfile Source

breitnet

New Member
Joined
Jan 18, 2018
Messages
13
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.


Code:
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
This should help U figure it out. Dave
Code:
MsgBox "SourcePath " & SourcePath & fileExtn & "  Destination " & DestinationPath
If FSO.fileexists(SourcePath & fileExtn) Then
FSO.copyfile Source:=SourcePath & fileExtn, Destination:=DestinationPath
End If
 

breitnet

New Member
Joined
Jan 18, 2018
Messages
13
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
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

New Member
Joined
Jan 18, 2018
Messages
13
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
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....
Code:
7UL1_A_10_WKA_200TT_AA.mpf
Dave
 

breitnet

New Member
Joined
Jan 18, 2018
Messages
13
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,261
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top