Solved: Rename a text file from within Excel

[alstubna]

Hello !

I have a macro that imports data from a text file (*.txt).

The name of this text file is in cell A1:

Parts List 2008 - Vol 1.txt

After the data is imported it's sliced, diced, moved around and whatnot.

As the last action of the macro I would like it to rename the text file to a name I have in cell B1 that's put together by a formula:

=LEFT(A1,LEN(A1)-4)&"(P).txt"

Results in the new desired name:

Parts List 2008 - Vol 1(P).txt

Renaming the file lets me know that that particular data has been processed.

Currently, after I'm done with the spreadsheet file I close Excel and go into Windows Explorer to rename the text file manually. If I could automate this is would be the icing on the cake.

Thanks for any assistance you can offer.

Al Stubna

[tstav]

One way to do it is by copying it to a new file with the desired name and then deleting the original file.
Another way would be by using FileSystemObject (same thing -copy/delete- though).

Method 1 (Cells A1, B1 should contain Path+FileName)
[vba]Sub Test_RenameFile()
On Error GoTo ErrorHandler
FileCopy Range("A1").value Range("B1").value
Kill Range("A1").Value
ErrorHandler:
MsgBox "Error" 'be more elaborate here
End Sub[/vba]

Method 2 (again, use error handling)
[vba]Sub Test_RenameFile()
Dim objFile As Object
Set objFile = CreateObject("Scripting.FileSystemObject")
objFile.CopyFile Range("A1").Value, Range("B1").Value, True
objFile.DeleteFile Range("A1").Value, True
Set objFile = Nothing
End Sub[/vba]

[Bob Phillips]

You don't even need for the file to be open, so you can process it, close it, then issue

[vba]

Name "C:\test\Trails.txt" As "C:\test\" & Range("B1").Value & ".txt"
[/vba]

[alstubna]

Thanks guys!

I used xld's method and it works great.

Easier than I thought.