macro for renaming

Its been a few years since Ive played with VB. I was trying to create a macro that would rename files but Ive never made one. Id like to enter the current name in Column A and in Column B the desired output. Can anyone recommend any good tutorials?

Some examples of names would help. Also, does the macro actually need to rename the files, or are you just using it to produce and list the file names?

well I have 100 image files

Some are old names which Ive pulled with bash to identify the contents of the folder. I want to take those names in the .txt file, add them to Column A. In column B i want to add the new name for the images. I would code inside a btn and once clicked the old files will be renamed the desired output of Column B


Example:

Current files A,B,C,D

A > 1234AAA
B > 3425BBB
C > 97852CCC
D > 321DDDD

the name isnt constant all that is constant is the row.

Try this:

Sub rename_images()


    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet3")
    Dim lastrow As Long
    lastrow = ws.Range("A1048576").End(xlUp).Row
    Dim rng As Range
    Set rng = ws.Range("A11:B" & lastrow) ' A is old name column, B is new name column, and 11 is the first row containing data
    Dim strNameOLD As String
    Dim strNameNEW As String
    Dim strPath As String
    strPath = wb.Path & "\"
    Dim rw As Long
    
    For rw = 1 To rng.Rows.Count
        With rng
            strNameOLD = .Cells(rw, 1).Value
            strNameNEW = .Cells(rw, 2).Value
            Name strPath & strNameOLD As strPath & strNameNEW
        End With
    Next rw
    
End Sub

nice. Sorry but i learn from understanding what is going on.

Can you explain the purpose of ws.Ranger("A1048576") please

strPath is a string correct??

wb.path & "\" will use the current location the file resides?

by 11 row containing data I would assume to script is starting on row 11?

nice. Sorry but i learn from understanding what is going on.

No worries; happy to explain better.

Can you explain the purpose of ws.Ranger("A1048576") please

To find the end the last row of data, we go to the very last cell of the longest column(Column A, in my example), and then we go to the "End" of the contiguous range, moving up (xlUP). The first cell with data will be our last row. Note that this assumes you don't have any thing else below the file names. Check out this link for a better understanding of how this works: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx. Once we have our last row, we set the range to run from our first to last rows, across the appropriate columns

set rng = ws.Range("A11:B" & lastrow)

strPath is a string correct??

Yes, I typically start all my string variable names with "str", then append something, in this case "Path", to tell me what the variable means

wb.path & "\" will use the current location the file resides?

Yes. The "Name" function requires the workbook path, not just the name. The full string needs to be like: "Drive:\Folder\FileName.extension". "wb.Path" = The path to the current workbook. You can manually add a path to another folder (e.g., strPath = "C:\MyFolder\"

by 11 row containing data I would assume to script is starting on row 11?

Yes. I almost always start my data on row 11, with headers on row 10, just to leave some room at the top. You can reset to whatever row is appropriate for your needs. (rw = the row number within the range, not within the whole workbook.)

I get a runtime error '5' invalid procedure call or argument

when I click debug it highlights this line:
Name strPath & strNameOLD As strPath & strNameNEW

I did modify my path to my desktop as strPath = wb.Path & "C:\Documents and Settings\Desktop\renamer"

range I set to 60000 since I will never go over that: lastrow = ws.Range("A60000").End(xlUp).Row

You need a "\" at the end of strPath.

Setting the range to 60000 is fine, but doesn't really matter; this isn't an operation in which the number of rows is going to have a significant affect on performance. 1048576 is the last row in 2007+. If you need to support older workbooks, the last row is 65536. I'd use one of those numbers and memorize both, then, when developing new macros, you'll never need to worry about how long the data will be; just go to the bottom and search up.

Nope, that's where I meant. The code works fine for me, so I'll need to see your worksheet to troubleshoot. Did you check to see if any of them worked before the error? In other words, is it failing at the start of the procedure, or is it just some specific file names that are throwing the error?