Can I user input to seek out particular files and copy/move them?

  • Thread starter Thread starter silkworm
  • Start date Start date
S

silkworm

The files I work with have names like : "16A-11H1H_JMDI4_265PH.CHP"
where "265PH" is a project name.

I want to write a Excel VBA scrpit, to prompt user to enter a project
name such as "265PH" and have the program to look into a directory to
copy any files with those characters in it. I've read about the

FileCopy Statement
or
FileSystemObject.CopyFile "c:\mydocuments\letters\*.doc",
"c:\tempfolder\"

I think I need do something like "c:\mydocuments\letters\*265PH.CHP",
but "265PH" needs to be a user specified input.

My question is that how do I pass a string variable that contain the
user input characters into the file name when the file name and path is
in double qoutation marks?


Thanks for the help
 
Option Explicit
Sub testme()

Dim FSO As Scripting.FileSystemObject
Dim ProjName As String

ProjName = InputBox(Prompt:="Enter the project name")
If ProjName = "" Then
Exit Sub 'cancel
End If

Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSO = New Scripting.FileSystemObject

FSO.CopyFile _
Source:="C:\my documents\excel\" & "*" & ProjName & ".xls", _
Destination:="C:\temp", _
overwritefiles:=True

End Sub
 
Back
Top