Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-26-2014, 02:59 PM
chrisd2000 chrisd2000 is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 64bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2007
Novice
Can anyone here tweek this macro for renaming Excel files based on a cell's contents?
 
Join Date: Jun 2014
Posts: 16
chrisd2000 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-27-2014, 10:10 AM
charlesdh charlesdh is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 32bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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?
Reply With Quote
  #3  
Old 06-27-2014, 10:52 AM
charlesdh charlesdh is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 32bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-27-2014, 11:01 AM
chrisd2000 chrisd2000 is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 64bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2007
Novice
Can anyone here tweek this macro for renaming Excel files based on a cell's contents?
 
Join Date: Jun 2014
Posts: 16
chrisd2000 is on a distinguished road
Smile 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
Reply With Quote
  #5  
Old 06-28-2014, 11:26 AM
charlesdh charlesdh is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 32bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I re-tested your code. I changed this

Code:
StrNewfullfilename = ThisWorkbook.Sheets(1).Range("B1").Value & "." & strExtension'''
Back to your original code

Code:
StrNewfullfilename = wb.Sheets(1).Range("B1").Value & "." & strExtension
When I ran the code if made the first change and then a prompt came up asking for the next "Name".
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
Reply With Quote
  #6  
Old 07-01-2014, 10:06 AM
charlesdh charlesdh is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 32bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

This was apparently solved on another site.

http://www.excelforum.com/excel-prog...-contents.html
Reply With Quote
  #7  
Old 07-01-2014, 01:53 PM
chrisd2000 chrisd2000 is offline Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Windows 7 64bit Can anyone here tweek this macro for renaming Excel files based on a cell's contents? Office 2007
Novice
Can anyone here tweek this macro for renaming Excel files based on a cell's contents?
 
Join Date: Jun 2014
Posts: 16
chrisd2000 is on a distinguished road
Default 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
Reply With Quote
Reply

Tags
batch rename, filename

Thread Tools
Display Modes


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
Can anyone here tweek this macro for renaming Excel files based on a cell's contents? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:52 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft