Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
We need to change the file names of 65 files by adding the same 1 word to the
beginning of each file name. Is it possible to do a global save as so we do not have to re-name each file? thank you in advance! jane |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
Assuming that all files are in the same folder, and that the folder doesn't
contain files that you don't want to rename, use code like the following: Sub RenameAll() Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS Dim FName As String Dim NewName As String Dim Pos As Integer ChDrive C_FOLDER_NAME ChDir C_FOLDER_NAME FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need. Do Until FName = vbNullString Pos = InStrRev(FName, ".") NewName = Left(FName, Pos - 1) & C_WORD_TO_APPEND & Mid(FName, Pos) Name FName As NewName FName = Dir() Loop End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jane" wrote in message ... We need to change the file names of 65 files by adding the same 1 word to the beginning of each file name. Is it possible to do a global save as so we do not have to re-name each file? thank you in advance! jane |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
You could try a macro - this appears to work okay for me, but it is a good
idea to back up your files before trying anything new. You will need to check the Microsoft Scripting Runtime library in the VBA editor under Tools/References and change the variables for your path and prefix. Sub Rename() Const strPath As String = "I:\Excel\Temp" '<< CHANGE Const strPrefix As String = "Prefix" '<<CHANGE Dim scrFSO As Scripting.FileSystemObject Dim scrFolder As Scripting.Folder Dim scrFile As Scripting.File Set scrFSO = New Scripting.FileSystemObject Set scrFolder = scrFSO.GetFolder(strPath) For Each scrFile In scrFolder.Files scrFile.Name = strPrefix & scrFile.Name Next scrFile End Sub "Jane" wrote: We need to change the file names of 65 files by adding the same 1 word to the beginning of each file name. Is it possible to do a global save as so we do not have to re-name each file? thank you in advance! jane |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
Chip
I found that your code did not add the text to the beginning of the original filename as OP desired I changed to........... Sub RenameAll() Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS Dim FName As String Dim NewName As String Dim Pos As Integer ChDrive C_FOLDER_NAME ChDir C_FOLDER_NAME FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need. Do Until FName = vbNullString NewName = C_WORD_TO_APPEND & FName Name FName As NewName FName = Dir() Loop End Sub OK with you? Gord Dibben MS Excel MVP On Thu, 15 Feb 2007 18:46:42 -0600, "Chip Pearson" wrote: Assuming that all files are in the same folder, and that the folder doesn't contain files that you don't want to rename, use code like the following: Sub RenameAll() Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS Dim FName As String Dim NewName As String Dim Pos As Integer ChDrive C_FOLDER_NAME ChDir C_FOLDER_NAME FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need. Do Until FName = vbNullString Pos = InStrRev(FName, ".") NewName = Left(FName, Pos - 1) & C_WORD_TO_APPEND & Mid(FName, Pos) Name FName As NewName FName = Dir() Loop End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
I know. I realized that after I posted. I was thinking "suffix" not
"prefix". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Chip I found that your code did not add the text to the beginning of the original filename as OP desired I changed to........... Sub RenameAll() Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS Dim FName As String Dim NewName As String Dim Pos As Integer ChDrive C_FOLDER_NAME ChDir C_FOLDER_NAME FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need. Do Until FName = vbNullString NewName = C_WORD_TO_APPEND & FName Name FName As NewName FName = Dir() Loop End Sub OK with you? Gord Dibben MS Excel MVP On Thu, 15 Feb 2007 18:46:42 -0600, "Chip Pearson" wrote: Assuming that all files are in the same folder, and that the folder doesn't contain files that you don't want to rename, use code like the following: Sub RenameAll() Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS Dim FName As String Dim NewName As String Dim Pos As Integer ChDrive C_FOLDER_NAME ChDir C_FOLDER_NAME FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need. Do Until FName = vbNullString Pos = InStrRev(FName, ".") NewName = Left(FName, Pos - 1) & C_WORD_TO_APPEND & Mid(FName, Pos) Name FName As NewName FName = Dir() Loop End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
wow! thank you for your responses!
We are a bit shaky on our macro skills but will do our best to implement your solutions. By chance is there a non macro solution? Thought I would ask :) thank you again! Jane "Jane" wrote: We need to change the file names of 65 files by adding the same 1 word to the beginning of each file name. Is it possible to do a global save as so we do not have to re-name each file? thank you in advance! jane |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
This may be useful to take a peek at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm BTW - my suggestion also assumed all of the files were in one folder and all of the files in that folder are supposed to be renamed. "Jane" wrote: wow! thank you for your responses! We are a bit shaky on our macro skills but will do our best to implement your solutions. By chance is there a non macro solution? Thought I would ask :) thank you again! Jane "Jane" wrote: We need to change the file names of 65 files by adding the same 1 word to the beginning of each file name. Is it possible to do a global save as so we do not have to re-name each file? thank you in advance! jane |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Global Save As?
yes, you are right that all files are within 1 folders altho we do have a
number of folders that are broken down by product type so we assume that this process would have to be done to each folder. Will take a look at the link you provided. THANKS! "JMB" wrote: This may be useful to take a peek at: http://www.mvps.org/dmcritchie/excel/getstarted.htm BTW - my suggestion also assumed all of the files were in one folder and all of the files in that folder are supposed to be renamed. "Jane" wrote: wow! thank you for your responses! We are a bit shaky on our macro skills but will do our best to implement your solutions. By chance is there a non macro solution? Thought I would ask :) thank you again! Jane "Jane" wrote: We need to change the file names of 65 files by adding the same 1 word to the beginning of each file name. Is it possible to do a global save as so we do not have to re-name each file? thank you in advance! jane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Global macros | Excel Discussion (Misc queries) | |||
Global changes to links | Charts and Charting in Excel | |||
Charts and "global" changes | Charts and Charting in Excel | |||
Global Function | Excel Discussion (Misc queries) | |||
Global Group | Excel Worksheet Functions |