How to Change the File Names of Selected Rows using Excel VBA

Question

I'm trying to write a VBA macro that changes file names from the text in Column B to the text of Column A. For example, if I had:

Column A: Stack Overflow

Column B: Question

It would change Question.txt to Stack Overflow.txt. As of now I've slightly modified the code from the answer here to read:

Sub rename()

Dim Source As Range
Dim OldFile As String
Dim NewFile As String

Set Source = Cells(1, 1).CurrentRegion

For Row = 2 To Source.Rows.Count
    OldFile = ActiveSheet.Range("D1").Value & ("\") & ActiveSheet.Cells(Row, 1) & (".pdf")
    NewFile = ActiveSheet.Range("D1").Value & ("\") & ActiveSheet.Cells(Row, 2) & (".pdf")

    ' rename files
    Name OldFile As NewFile

Next
End Sub

This works great, but I'm trying to get it to only run on selected rows; my ideal end result is that I can select the 15 non-consecutive rows that I want to change, run the macro, and have it only apply to those 15. I tried the below code but the ActiveSheet.Cells(Row, 1) function is returning a Run-Time Error 1004, Application-defined or object-definied error; is there a good way around this?

Sub renameMain()

Dim OldFile As String
Dim NewFile As String
Dim rng As Range

Set rng = Selection

For Each Row In rng
    OldFile = ActiveSheet.Range("O1").Value & "\" & ActiveSheet.Range(Row, 2) & ".pdf"
    NewFile = ActiveSheet.Range("O1").Value & "\" & ActiveSheet.Range(Row, 1) & ".pdf"

    ' rename files
   Name OldFile As NewFile

Next Row
End Sub

Any advice would be much appreciated!

@findwindow It is a (relatively little used) VBA statement which can move and rename files -- part of pre-VBScript old-school VB programming, before people started using File System Object for such things. — John Coleman, Aug 17 '15 at 21:52
@findwindow It is in the O'Reilly book "VB & VBA in a Nutshell" by Paul Lomax -- a book which I highly recommend to any VBA programmer. — John Coleman, Aug 17 '15 at 21:56

score 0 · Answer 1 · answered Aug 17 '15 at 21:49

You seem to want to use Row as an int variable. It isn't. Maybe try this:

Sub renameMain()

Dim OldFile As String
Dim NewFile As String
Dim rng As Range
Dim i as long

Set rng = Selection

For i = 1 To rng.Rows.Count
    OldFile = ActiveSheet.Range("O1").Value & "\" & rng.Cells(i, 2) & ".pdf"
    NewFile = ActiveSheet.Range("O1").Value & "\" & rng.Cells(i, 1) & ".pdf"

    ' rename files
   Name OldFile As NewFile

Next i
End Sub

score 0 · Accepted Answer · answered Aug 17 '15 at 22:07

Non contiguous rows in the Selection object can be accessed using its .Areas collection:

Option Explicit

Sub renameMain()
    Dim oldFile As String, newFile As String
    Dim selArea As Range, selRow As Range, staticVal As String

    With ActiveSheet
        staticVal = .Range("O1").Value2 & "\"
        For Each selArea In Selection.Areas
            For Each selRow In selArea.Rows
                oldFile = staticVal & .Cells(selRow.Row, 2).Value2
                newFile = staticVal & .Cells(selRow.Row, 1).Value2
                Name oldFile & ".pdf" As newFile & ".pdf"   'rename files
            Next
        Next
    End With
End Sub

How to Change the File Names of Selected Rows using Excel VBA

2 Answers2