Excel VBA File Copy

Status
Not open for further replies.

breitnet

New Member
Joined
Jan 18, 2018
Messages
13
Hello,

I am looking for some help copying from a source file to a destination file.

sourcePath = Sheet1.Cells(121, 9).Value
Source Cell: (121, 9): "C:\Desktop\Proc_9UL_RB_10\MCD_9UL_RB_10"

DestinationPath = Sheet1.Cells(110, 13).Value
Destination Cell(110,13): "C:\Desktop\Proc_9UL_RB_10\MCD_9UL_RB_10_Audit\MCD_9UL0_RB_10"

fileExtn = Sheet1.Cells(115, 4).Value & "*.mpf"
File Extension Cell(115,4): 9UL0

Source path is only be a partial file name at the end. Ex:C:\Desktop\Proc_9UL_RB_10\MCD_9UL_RB_10\9UL0*.mpf

Actual source with full file name. Ex: C:\Desktop\Proc_9UL_RB_10\MCD_9UL_RB_10\9UL0_T_10_FSA_193RP_AA.mpf

I don't want to look the entire path because there are many variations, the only constant are the first four characters (9UL0).

I am having an issue using the If statement. IF 9UL0*.mpf exists copy to destination, If not skip file copy.

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

'copies source path to destination path if file exists.
MsgBox "SourcePath " & sourcePath & fileExtn & "  Destination " & DestinationPath
If FSO.fileexists(sourcePath & fileExtn) Then
FSO.copyfile Source:=sourcePath & fileExtn, Destination:=DestinationPath
End If
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,099,701
Messages
5,470,280
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top