|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can anyone here tweek this macro for renaming Excel files based on a cell's contents?
Can anyone here tweek this macro for renaming Excel files based on a cell's contents?
I use it to rename all the files in a folder based on the text that exists in cell B1. Can you add some more code which would give me a prompt to enter 7 more characters to put right before .xls? Here is the existing VBA code. Sub RenameAllExcelFilesInDirectory() With Application.FileDialog(msoFileDialogFolderPicker) .Show filepath = .SelectedItems(1) End With Set r = Workbooks.Add.Worksheets(1).Range("A1") StrFile = Dir(filepath & "\*.*") Do While Len(StrFile) > 0 strExtension = Split(StrFile, ".")(UBound(Split(StrFile, "."))) Set wb = Workbooks.Open(filepath & "\" & StrFile) StrNewfullfilename = wb.Sheets(1).Range("B1").Value & "." & strExtension wb.Close r.Value = StrFile 'print old name r.Offset(, 1).Value = StrNewfullfilename 'print new name Set r = r.Offset(1) Name filepath & "\" & StrFile As filepath & "\" & StrNewfullfilename StrFile = Dir Loop End Sub |
#2
|
|||
|
|||
Hi,
It looks as if you want to use the Data in B1 to Name a file and then loop back for the next file and name it. But you want to use a input box for the next name. In stead of that can you not put the 1st change in B1 then in B2 the next change and loop down column B? |
#3
|
|||
|
|||
Hi,
Take a look at this it may help. You can make correction to it. I was not sure about "wb". Code:
Sub RenameAllExcelFilesInDirectory() Dim iReply As String ''' Added With Application.FileDialog(msoFileDialogFolderPicker) .Show filepath = .SelectedItems(1) End With Set r = Workbooks.Add.Worksheets(1).Range("A1") StrFile = Dir(filepath & "\*.*") Do While Len(StrFile) > 0 strExtension = Split(StrFile, ".")(UBound(Split(StrFile, "."))) Set wb = Workbooks.Open(filepath & "\" & StrFile) StrNewfullfilename = ThisWorkbook.Sheets(1).Range("B1").Value & "." & strExtension''' added this workbook name not sure if this is what u wanted wb.Close r.Value = StrFile 'print old name r.Offset(, 1).Value = StrNewfullfilename 'print new name Set r = r.Offset(1) Name filepath & "\" & StrFile As filepath & "\" & StrNewfullfilename StrFile = Dir iReply = InputBox(Prompt:="Please Enter Next File", _ Title:="Update File") If iReply = "" Then Exit Sub ThisWorkbook.Sheets(1).Range("B1").Value = iReply Loop End Sub |
#4
|
|||
|
|||
About the macro tweeks
To understand what the code does, it would be best to run the code which works fine. The code seems to only work on one file at a time but ends up renaming all of the files in a folder which is what I want.
I would like to see if someone can add some more code to do the following. (1) Generate a prompt to enter characters to put both right before .xls and in cell A1. I would like the suffix to be the same for all of the files. (2) Copy the font formatting from cell B1 to cell A1 (3) Put the date and time in cell W1 that is found in cell B2. The text in cell B2 always has the following format "Requested 12345678901234567 (US) on 06/26/2014, 13:58" where "12345678901234567" represents a unique string about 10-17 characters long. Thanks for working on this code which I guess I should soon learn to do myself. I guess I hadn't explained what I wanted very well. Last edited by chrisd2000; 06-27-2014 at 12:54 PM. Reason: clarification |
#5
|
|||
|
|||
Hi,
I re-tested your code. I changed this Code:
StrNewfullfilename = ThisWorkbook.Sheets(1).Range("B1").Value & "." & strExtension''' Code:
StrNewfullfilename = wb.Sheets(1).Range("B1").Value & "." & strExtension In the book created it has in column A the old file name. And in column B the new file name. It looped through all of the files in the "Selected" folder. Note: It does not do what you indicated in your last post. It does as you indicated in the first question. Last edited by charlesdh; 06-28-2014 at 11:31 AM. Reason: added note |
#6
|
|||
|
|||
Hi,
This was apparently solved on another site. http://www.excelforum.com/excel-prog...-contents.html |
#7
|
|||
|
|||
My IT guy says I have to know what the code does so that I know it's not malicious
My IT guy says I have to know what macro code does before I can use it so that I am not running malicious code. I got someone to alter it so that I can put in the Walmart calendar week into the filename. So I am trying to Google various parts of the code and I have found some useful explanations. Can you fill in the gaps? Here is what I found so far:
With Application.FileDialog(msoFileDialogFolderPicker) selects a folder from a certain path and import all the files inside it http://stackoverflow.com/questions/1...cker-using-vba filepath = .SelectedItems(1) ' This part is involved in putting the filepath of the selected file in the variable filepath - See more at: http://lroedal.net/en/vba/read-from-....Od11itRI.dpuf or http://lroedal.net/en/vba/read-from-text-file Set wb = Workbooks.Open - The line that starts with this text uses the file path you selected in FileDialog - http://stackoverflow.com/questions/2...-manipulate-it |
Tags |
batch rename, filename |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can a macro rename Excel files based on a cellname? | chrisd2000 | Excel Programming | 1 | 06-23-2014 06:50 PM |
Excel 2007, highlight entire row based on data found in one cell | MSofficeBLUE | Excel | 2 | 10-15-2013 09:51 PM |
Image Renaming Macro? | jammer | PowerPoint | 5 | 07-15-2013 09:21 AM |
Change formula cell range based on cell value | Scoth | Excel | 4 | 10-25-2012 07:51 AM |
Macro based on cell value | ubns | Excel Programming | 1 | 05-07-2012 04:03 AM |