Add a code or number at the end of the file name using VBA (Looped)

[rdtoner]

Hi Have a VBA code that loops (Through multiple files) and deletes certain cells but I want to add in a addition to the already named file name.

I basically need a code to add into the current one and a code that will just add either numbers or letters to the end of the file name.

This is the code for the loop

Sub AllWorkbooks()
Dim MyFolder As String 'Path collected from the folder picker dialog
Dim MyFile As String 'Filename obtained by DIR function
Dim wbk As Workbook 'Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
Do While MyFile <> ""
'Opens the file and assigns to the wbk variable for future use
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile & 1)
'Replace the line below with the statements you would want your macro to perform
Sheets(1).Range("GM:LH").Delete

wbk.Close savechanges:=True

MyFile = Dir 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
MsgBox "Module 2 Complete"
End Sub

This is the code I have found that can put the number at the beginning but can't work out how to get the code to add at the end

Sub AddSalesPrefix()
Dim Path As String, Filename As String
Path = "c:\Users\m_aatif\Desktop\Test\"
Filename = Dir(Path & "*", vbNormal)
Do While Len(Filename)
Name Path & Filename As Path & "Sales " & Filename
Filename = Dir()
Loop
End Sub

thanks for any help anyone can give

 

[HighAndWilder]

Hi Have a VBA code that loops (Through multiple files) and deletes certain cells but I want to add in a addition to the already named file name.

I basically need a code to add into the current one and a code that will just add either numbers or letters to the end of the file name.

This is the code for the loop

Sub AllWorkbooks()
Dim MyFolder As String 'Path collected from the folder picker dialog
Dim MyFile As String 'Filename obtained by DIR function
Dim wbk As Workbook 'Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
Do While MyFile <> ""
'Opens the file and assigns to the wbk variable for future use
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile & 1)
'Replace the line below with the statements you would want your macro to perform
Sheets(1).Range("GM:LH").Delete

wbk.Close savechanges:=True

MyFile = Dir 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
MsgBox "Module 2 Complete"
End Sub

This is the code I have found that can put the number at the beginning but can't work out how to get the code to add at the end

Sub AddSalesPrefix()
Dim Path As String, Filename As String
Path = "c:\Users\m_aatif\Desktop\Test\"
Filename = Dir(Path & "*", vbNormal)
Do While Len(Filename)
Name Path & Filename As Path & "Sales " & Filename
Filename = Dir()
Loop
End Sub

thanks for any help anyone can give

Try this on a COPY of your files.

Change the code after this REM line as appropriate.

' Substitue 'prefix-' and '-suffix" in the next line of code as appropriate.

Sub AddSalesPrefix()
Dim Path As String, Filename As String

  Path = "c:\Users\m_aatif\Desktop\Test\"
  
  Filename = Dir(Path & "*", vbNormal)
  
  Do While Len(Filename)
  
    ' Substitue 'prefix-' and '-suffix" in the next line of code as appropriate.
    
    Name Path & Filename As Path & "prefix-" & Left(Filename, _
      (InStrRev(Filename, ".", -1, vbTextCompare) - 1)) & "-suffix" & Mid(Filename, _
      InStrRev(Filename, ".", -1, vbTextCompare))
  
    Filename = Dir()
  
  Loop
  
End Sub

 

[rdtoner]

Try this on a COPY of your files.

Change the code after this REM line as appropriate.

' Substitue 'prefix-' and '-suffix" in the next line of code as appropriate.

Sub AddSalesPrefix()
Dim Path As String, Filename As String

  Path = "c:\Users\m_aatif\Desktop\Test\"
 
  Filename = Dir(Path & "*", vbNormal)
 
  Do While Len(Filename)
 
    ' Substitue 'prefix-' and '-suffix" in the next line of code as appropriate.
   
    Name Path & Filename As Path & "prefix-" & Left(Filename, _
      (InStrRev(Filename, ".", -1, vbTextCompare) - 1)) & "-suffix" & Mid(Filename, _
      InStrRev(Filename, ".", -1, vbTextCompare))
 
    Filename = Dir()
 
  Loop
 
End Sub

This worked a treat and I like that I have a prefix too if ever I need, I just kept that blank and changed the prefix, this is going to save me so much time.

 

[HighAndWilder]

This worked a treat and I like that I have a prefix too if ever I need, I just kept that blank and changed the prefix, this is going to save me so much time.

Good. Welcome to Mr Excel.

Investigate XL2BB when you have time, to be used when you want to post worksheets.

XL2BB - Excel Range to BBCode