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



silkworm wrote:

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

--

Dave Peterson

Thanks for the reply.