rename folder o files with value from inside each wbk

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
 
R

Rob van Gelder

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
 
M

Max Bialystock

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...
 
Top