In finding file name with FileCopy, ignore last part of name

[SteveC]

Does anyone know what code to use that would look only for the file beginning with "InvoiceTemplate" and ignore the part of the file name "(1.25)" that comes after? (This is a version number that will change.)

FileCopy YourDrive.driveletter & ":My DocumentsHorses" & _
"TemplatesInvoiceTemplate(1.25).xls", _
ActiveWorkbook.Path & "NewlyCopiedInvoiceTemplate.xls"

Thank you.
Steve


[ This Message was edited by: SteveC on 2002-05-27 15:05 ]

[Robb__]

Steve

Try using FileSearch to oocate the file as it allows use of wildcards. Code would be something like this:

Sub FindandCopy()
With Application.FileSearch
.NewSearch
.LookIn = "E:"
.SearchSubFolders = True
.FileName = "TemplatesInvoiceTemplate*.xls"
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then FileCopy .FoundFiles(1), ActiveWorkbook.Path & _
"NewlyCopiedInvoiceTemplate.xls"
End With
End Sub


You may need to play around with the paths etc to suit your requirements.

Any help?

Regards

Robb__

[SteveC]

* PROBLEM: This new code works with only one drive letter, unlike my current code (at bottom).
I've tried combining the two without luck...but I'm a beginner.
Anyone have a suggestion for modifying either one, below?
----
NEWLY PROPOSED CODE (but the LookIn seems to be limited to only one drive):

Sub FindAndCopyInvoiceTemplate()
With Application.FileSearch
.NewSearch
.LookIn = "C:My DocumentsHorsesTemplates"
.SearchSubFolders = True
.FileName = "InvoiceTemplate*.xls"
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then FileCopy .FoundFiles(1), ActiveWorkbook.Path & _
"NewlyCopiedInvoiceTemplate.xls"
End With
End Sub
----
**MY CURRENT CODE (works, but it needs to ignore the "(1.25)" when looking for the file name):

Sub CopyInvoiceTemplate()
' COPY INVOICE TEMPLATE to HORSE'S FOLDER
'5/25/2002 from TsTom
Dim FS, d, YourDrive
Set FS = CreateObject("Scripting.FileSystemObject")
Set YourDrive = FS.GetDrive(FS.GetDriveName(ActiveWorkbook.Path))
FileCopy YourDrive.driveletter & ":My DocumentsHorses" & _
"TemplatesInvoiceTemplate(1.25).xls", _
ActiveWorkbook.Path & "NewlyCopiedInvoiceTemplate.xls"
End Sub

[SteveC]

Well, I can't get any further on this.
Can anyone help?
Steve

[Robb__]

Steve

It looks as though the workbook you want to locate will be in the same drive as the workbook in which you have the code. If so, try replacing:

.LookIn = "C.....etc" with

.LookIn = Left(ActiveWorkbook.Path,1) & "My Documents......etc"

Any help?

Regards

Robb__