Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-16-2021, 10:45 AM
JustJim JustJim is offline Strip out numbers from file names in a directory. Windows 7 64bit Strip out numbers from file names in a directory. Office 2007
Novice
Strip out numbers from file names in a directory.
 
Join Date: Oct 2021
Posts: 1
JustJim is on a distinguished road
Default Strip out numbers from file names in a directory.

Greetings,



I have a bunch of Word files in a directory with names like this one:

46468-1448197 - Smith-Bob-Sep 12, 2021 220 PM

The file names have a consistent pattern throughout. Each begins with a hyphenated serial number(?) with the same number of digits, followed by a space, followed by a hyphen, followed by a space. Beats me why the source of these files would name them that way, but that was not my call.

I want to run a macro that truncates the names of all files within a selected directory up to and including the " - ", so that what is left is merely:

Smith-Bob-Sep 12, 2021 220 PM

I looked for and noticed bits and pieces of code that provide a partial solution but I cannot piece it together.

Your kind help is much appreciated.

Jim.
Reply With Quote
  #2  
Old 10-20-2021, 06:46 AM
TheBigBoss TheBigBoss is offline Strip out numbers from file names in a directory. Windows 7 32bit Strip out numbers from file names in a directory. Office 2010 32bit
Advanced Beginner
 
Join Date: Dec 2016
Posts: 56
TheBigBoss is on a distinguished road
Default

Hi Jim,

I am a "novice" but I am wondering since you want to do basic file renaming, the best would be use a script shell (.bat on Windows) or server script (ASP/PHP/Python, etc.) depending where the files are located.

For Visual Basic, please have a look at the Split() function.
Split function (Visual Basic for Applications) | Microsoft Docs

You can just split the string at the "-" delimiter and retrieve the last value of the array (you will have to remove the empty space of that last value)
Reply With Quote
  #3  
Old 10-20-2021, 06:40 PM
Guessed's Avatar
Guessed Guessed is offline Strip out numbers from file names in a directory. Windows 10 Strip out numbers from file names in a directory. Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Standing on the shoulders of the code already provided by Peterson here https://www.msofficeforums.com/163214-post3.html
Code:
Sub RenameFile()
  ' Before running the code, click Tools > References in the Visual Basic Editor and
  ' make sure that "Microsoft Scripting Runtime" is checked.
  Dim strPath As String, objFile As File, sName As String, sNewName As String, sNewPath As String
  Dim objFolder As Folder, objFSO As Scripting.FileSystemObject
  
  ' Put your path here:
  strPath = "C:\Users\username\MyFiles\"
  
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  If objFSO.FolderExists(strPath) Then
    Set objFolder = objFSO.GetFolder(strPath)
    For Each objFile In objFSO.GetFolder(strPath).Files   'Loop through all files in the folder
      sName = objFile.Name
      If Len(sName) > 16 And IsNumeric(Left(sName, 5)) Then   'If file starts with 5 digits and has a name longer that 16 characters
        sNewName = Trim(Mid(sName, 16))
        objFSO.CopyFile strPath & objFile.Name, strPath & sNewName, True
        objFSO.DeleteFile (objFile.Path)   ' Delete the original file
      End If
    Next objFile
  End If
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Tags
rename file, word vba code

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Strip out numbers from file names in a directory. Would like to shortern the coding for searching docx file names in specific directory SamDsouza Word VBA 4 01-06-2021 07:27 AM
Strip out numbers from file names in a directory. Using Word VBA to change file names in a directory sg11 Word VBA 4 03-22-2018 04:25 AM
Strip out numbers from file names in a directory. replace numbers with names shivy Excel 6 04-01-2017 10:15 AM
Strip out numbers from file names in a directory. How capture a folder's file names to a file? BobKnepper Word 2 05-22-2016 07:30 AM
Strip out numbers from file names in a directory. Copy and Paste from File to File but File Names always change aaronbauer1980 Excel Programming 1 04-15-2016 05:53 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:48 AM.


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