Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a .txt file via Excel macro
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 :) -- Tracey @ BrisVegas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a .txt file via Excel macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a .txt file via Excel macro
I'm not sure what you're doing, but you may want to look at Name and FileCopy in
VBA's help. Tracey wrote: 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 :) -- Tracey @ BrisVegas -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a .txt file via Excel macro
And the Kill statement, too.
Tracey wrote: 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 :) -- Tracey @ BrisVegas -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename a .txt file via Excel macro
You seem to be asking for code like this...
OldFN = "G:\My Reports\Transfers\stafflist.txt" NewFN = Replace(OldFN, "Transfers", Format(Date, _ "mmmm yy"), Compa=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. -- Rick (MVP - Excel) "Tracey" wrote in message ... 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 :) -- Tracey @ BrisVegas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename Worksheet Tab as File Name Using a Macro | Excel Discussion (Misc queries) | |||
rename excel file name | Excel Discussion (Misc queries) | |||
Batch file rename macro | Excel Programming | |||
Rename .txt file with macro | Excel Programming | |||
rename file macro | Excel Programming |