Excel VBA File Copy

breitnet

New Member
Joined
Jan 18, 2018
Messages
13
Hi, I have a macro I am working on and would like to get some help. I am trying to copy files from one folder to another folder based on a partial file name.

Here is the code I am currently using. It copies the entire contents of the folder (not what I want).

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

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

fileExtn = "*.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
MsgBox "Your files have been copied"

End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,043
Office Version
365
Platform
Windows
What is the partial filename?
 

breitnet

New Member
Joined
Jan 18, 2018
Messages
13
Here are the file name examples:

8HB0_RB_10.mpf --> Folder 1
8HB1_RB_10.mpf --> Folder 2
8HB2_RB_10.mpf --> Folder 3
8HB3_RB_10.mpf --> Folder 4
8HB4_RB_10.mpf --> Folder 5

These are located in a common folder and will be copied to another folder by the first 4 characters ex: "8HB0."
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,043
Office Version
365
Platform
Windows
Ok, make this change
Code:
fileExtn = "[COLOR=#ff0000]8HB0[/COLOR]*.mpf"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,043
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top