rename folder o files with value from inside each wbk

  • Thread starter Thread starter Max Bialystock
  • Start date Start date
M

Max Bialystock

I have a folder full of workbooks each of which takes its name from the
value in sheet 1 cell E1 (which is the date the workbook was created).
Each name is unique, but the format varies.
Is there a way to rename all the workbooks (using the value in sheet 1 cell
e1), so that they all have the same date format?

Any help at all will be much appreciated.

Thanks,
DL
 
Max,

Sub testit()
Dim strPath As String, strFilter As String, strFile As String, strTemp
As String
Dim arr() As String, i As Long, wkb As Workbook

strPath = "C:\T\"

ReDim arr(0)
strFile = Dir(strPath & "*.xls")
If strFile <> "" Then
Do Until strFile = ""
i = UBound(arr) + 1
ReDim Preserve arr(i)
arr(i) = strPath & strFile
strFile = Dir
Loop
End If
For i = 1 To UBound(arr)
Set wkb = Workbooks.Open(arr(i))
strTemp = Environ("COMSPEC") & " /c rename """ & arr(i) & """ """ &
_
Format(wkb.Worksheets(1).Range("E1"), "yyyymmdd") &
".xls"""
wkb.Close False
Set wkb = Nothing
Shell strTemp
Next
End Sub


Rob
 
Ron,

Bloody Marvellous!!!

Thank you!!!

Max



Rob van Gelder said:
Max,

Sub testit()
Dim strPath As String, strFilter As String, strFile As String, strTemp
As String
Dim arr() As String, i As Long, wkb As Workbook

strPath = "C:\T\"

ReDim arr(0)
strFile = Dir(strPath & "*.xls")
If strFile <> "" Then
Do Until strFile = ""
i = UBound(arr) + 1
ReDim Preserve arr(i)
arr(i) = strPath & strFile
strFile = Dir
Loop
End If
For i = 1 To UBound(arr)
Set wkb = Workbooks.Open(arr(i))
strTemp = Environ("COMSPEC") & " /c rename """ & arr(i) & """ """ &
_
Format(wkb.Worksheets(1).Range("E1"), "yyyymmdd") &
".xls"""
wkb.Close False
Set wkb = Nothing
Shell strTemp
Next
End Sub


Rob
Click to expand...
 
Back
Top