Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rename folder o files with value from inside each wbk
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
rename folder o files with value from inside each wbk
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 "Max Bialystock" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
rename folder o files with value from inside each wbk
Ron,
Bloody Marvellous!!! Thank you!!! Max "Rob van Gelder" wrote in message ... 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 "Max Bialystock" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
search folder/directory for a phrase inside excel files | Excel Discussion (Misc queries) | |||
How to create a copy of a folder having five files in it, & rename | Excel Worksheet Functions | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
I would like to print the titles of files inside a folder. | Excel Worksheet Functions |