Excel VBA Change Text Filename (remove time)

[michaelmc]

Hi Guys,

How do I programmatically change the file name of a txt file using excel vba, I need a script where it will go through a folder which consists of txt files and change the txt filename by removing Time.

Original Filename: ABC_ABCDE_ABCD_YYYYMMDDTTTTTT.txt
New Filename: ABC_ABCDE_ABCD_YYYYMMDD.txt

Thank you in advance

Mike

 

[michaelmc]

found the solution, posting it here for the benefit of other users

Sub LoopThroughFiles()


Dim txtfile As String, folderPath As String
Dim newName As String


folderPath = GetFolder()
txtfile = Dir(folderPath & "" & "*.txt")


While txtfile <> ""


If checkFormat(txtfile) = True Then
newName = Left(txtfile, 23) & ".txt"
On Error Resume Next
'rename file is done here
If Not txtfile = "" Then Name (folderPath + "" + txtfile) As (folderPath + "" + newName)
On Error GoTo 0


End If
txtfile = Dir


Wend
End Sub


'this function is for check format of file
'you may edit it as per your requirment
Function checkFormat(str As String) As Boolean
checkFormat = False
If Len(str) = 33 And Mid(str, 4, 1) = "_" Then
checkFormat = True
End If


End Function


'this function for select folder path
Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function