Rename a .txt file via Excel macro

[Tracey]

Hi All,

I would like to rename and save a .txt file from within an Excel macro. I
don't need to open the file just Save As to another location.

Currently:
G:\My Reports\Transfers\stafflist.txt

Needs to be:
G:\My Reports\December 09\stafflist 041209.txt

Is there some code that will do this for me - thanks in advance

 

[marcus]

Hi Tracey

There is probably a much easier way to acomplish this than the method
I propose. I assume you want to loop through a batch of files to
change the location and path accordingly.

I am not sure how to do it without opening the files. However this
method gets the job done. In the file you are running the code from,
place the date you appeded on the end of the text name in Cell A1, eg.
041209. This gives you flexibility if you need to run the procedure in
future. Run the following code.

It opens all the TXT files in a given directory and saves them to the
new directory.

Take care

Marcus

'OnCreekSt


Option Explicit
Sub Open_Txt()
Dim oWbk As Workbook
Dim wb As Workbook
Dim sFil As String
Dim sPath As String
Dim strFullName As String
Dim NewDir As String
Dim MyName As String
Dim WBname As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

sPath = "G:\My Reports\Transfers\"
NewDir = "G:\My Reports\December 09"
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
MyName = Range("A1").Value

Do While sFil <> ""
strFullName = sPath & "\" & sFil
Set oWbk = Workbooks.Open(strFullName)
WBname = Replace(oWbk.Name, ".xls", "")
oWbk.SaveAs Filename:=NewDir & "\" & WBname & MyName,
FileFormat:=xlText
oWbk.Close False
sFil = Dir
Loop

End Sub

 

[Dave Peterson]

I'm not sure what you're doing, but you may want to look at Name and FileCopy in
VBA's help.

 

[Dave Peterson]

And the Kill statement, too.

Hi All,

I would like to rename and save a .txt file from within an Excel macro. I
don't need to open the file just Save As to another location.

Currently:
G:\My Reports\Transfers\stafflist.txt

Needs to be:
G:\My Reports\December 09\stafflist 041209.txt

Is there some code that will do this for me - thanks in advance

 

[Rick Rothstein]

You seem to be asking for code like this...

OldFN = "G:\My Reports\Transfers\stafflist.txt"
NewFN = Replace(OldFN, "Transfers", Format(Date, _
"mmmm yy"), Compare:=vbTextCompare)
NewFN = Replace(NewFN, ".txt", " " & Format(Date, "ddmmyy") & ".txt")
Name OldFN As NewFN

where I have assumed your own code has retrieved the filename and path...
just assign it to the OldFN variable and use the rest of the code I posted
to change it to the format you want and to save it to the new location.