Renaming file in VBA

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi I have got this code on the web & its working fine.
I just need to add a condition is, while renaming the files, if its found that the same name already exists in the folder, then it will add an additional number at the end of the new name.
Please help me with this.

VBA Code:
Sub RenameAllFilesInaFolder()
 'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)
    'change the file format in below xls or xlsx
    
Dim objFile As Scripting.File
Dim objFolder As Scripting.Folder
Dim owbk As Workbook, twbk As Worksheet, ws As Worksheet
Dim cRow As Integer, fName As String, fol As String
Dim v As String, fv As String
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      .Show
      On Error Resume Next
      fol = .SelectedItems(1)
      Err.Clear
      On Error GoTo 0
    End With
    If fol = "" Then Exit Sub
   
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(fol)

For Each objFile In objFolder.Files
    Set twbk = ThisWorkbook.Sheets("Sheet1")
    cRow = twbk.Range("A" & Rows.Count).End(xlUp).Row
            Set owbk = Workbooks.Open(objFile)
                Set ws = owbk.Sheets(1)
                   'v = ws.[C3].Value 'file name from cell c3
                    v = "SS_" & ws.[C3].Value 'file name withe prefix and cell c3
                        twbk.Range("A" & cRow + 1).Value = v 'Change as need
                fv = v & ".xls" 'file format see below to save option for xls and xlsx
                fName = objFolder & "\" & fv
                              
                'save option for xls
                ws.SaveAs Filename:=fName, FileFormat:=xlExcel8, CreateBackup:=False
                Windows(fv).Close False
            Kill objFile
Next objFile
Application.ScreenUpdating = True
Set ws = Nothing
Set owbk = Nothing
Set twbk = Nothing
Set objFolder = Nothing
Set objFSO = Nothing
End Sub
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Can anyone help me with the above issues?
Hope some experts will look into this.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hope someone will look into this.
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Someone else has pointed out the problem in some other site, here was the problem.
VBA Code:
If fnum > 0 Then
       fv = v & " " & fnum & ".xls"
    Else
        fv = v & ".xls"
  End If

variable fnum have to set to 0, after completing the task, before starting the another.
Now I am having the file name without any increment number for the unique name.

Solution.
Code:
If fnum > 0 Then
  fv = v & " " & fnum & ".xls"
Else
  fv = v & ".xls"
End If

fnum = 0
 

Watch MrExcel Video

Forum statistics

Threads
1,127,491
Messages
5,625,075
Members
416,068
Latest member
seba_s

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top