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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

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