CODEWITHSUNDEEP
excelvbscript
Saf
Saf

Reputation: 127

Rename files where there are dates

I have some code that will rename a filename and rename the tab name in the file, however for my code to work my file name has to be static.

The problem I have is that my files arrive often with dates or week numbers added to the end of the file name, can somebody please help me modify my code so that it uses a wildcard or perhaps ignores any thing after the first two or three words of a file name and runs my code?

e.g I get a file called Incident Report Week 43.xlsx, so to make my code work I have to manually rename the file to Incident Report.xlsx, is there a way i can make it ignore the 'Week 43' or anything after the word Report, that same file can also be sometimes called Incident Report 20150424.xlsx

Here is what i have so far

'launch Excel and open file   
Const xlExcel8          = 56    
Const xlOpenXMLWorkbook = 51    
Set xlObj = CreateObject("Excel.Application")    
Set xlFile = xlObj.WorkBooks.Open("C:\Users\_ThisWeek\Incident Report.xlsx")    
'turn off screen alerts    
xlObj.Application.DisplayAlerts = FALSE    
'loop through sheets    
For Each Worksheet In xlFile.Worksheets     
'change sheet to desired worksheet name    
If Worksheet.Name = "Weekly Report" Then    
  Worksheet.Name = "Sheet1"    
End If    
Next    
xlfile.SaveAs "C:\Users\_ThisWeek\Incident Report.xls", xlExcel8    
xlFile.Close True    
xlObj.Quit

What Ansgar submitted works, tested, thanks sir

Upvotes: 2

Views: 52

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Add an outer loop to iterate over all files in the source directory and open only files with the extension .xlsx and a name beginning with Incident Report.

Set fso   = CreateObject("Scripting.FileSystemObject")
Set xlObj = CreateObject("Excel.Application")

Set re = New RegExp
re.Pattern = "^incident report.*\.xlsx$"
re.IgnoreCase = True

For Each f In fso.GetFolder("C:\Users\_ThisWeek").Files
  If re.Test(f.Name) Then
    Set xlFile = xlObj.WorkBooks.Open(f.Path)
    ...
    xlFile.Close True
  End If
Next

Upvotes: 1

Related Questions