Wanting to automate: Change name of several excel documents

[Silvie]

I have several excel documents (templates) all with the name
xxx_Testprotocol1, xxx_Testprotocol2 etc. For each test campaign I need to
replace the xxx at the beginning with the name of the testcampaign e.g.
RC27_Testprotocol1.

Is there a way of automating this since doing it manually is quite time
consuming.

Cheers
Silvie

 

[Joel]

Use code below. Read comments and change Folder as required.

Sub MakeCopy()

Folder = "c:\temp\"
'get all xlt files
TestCampaign = InputBox("Enter test Campaign : ")
FName = Dir(Folder & "*.xlt")
Do While FName <> ""
'make sure xlt files start with XXX
If UCase(Left(FName, 3)) = "XXX" Then
'remove extension
BaseName = Left(FName, InStr(FName, ".") - 1)
'remove three XX at the geginning of name
BaseName = Mid(BaseName, 4)
'copy file and change extension from xlt to xls
FileCopy Source:=Folder & FName, _
Destination:=Folder & TestCampaign & BaseName & "xls"
End If
FName = Dir()
Loop
End Sub