PLEASE HELP!!!!

[s_smith_iet]

I think the best way to start off is by explaining to you what I am
doing.

I have 5 machines that recored everything they do and save it in two
files, a txt and a dat file. When you put these two files together
they are called a data log.
The dat files has a bunch of numbers and the txt file has a bunch of
times and the steps and settings of the machine. For a while I would
have to manually get the numbers I needed but then I made a marco to
do this.
The macro i made (with lots of help) opens five of the datalogs (5 txt
and 5 dat files) pulls out the info I need and then copies that info
into a trend chart. Once that is done it closes them all and delets
them. The problem is that I have to manually move the five datalogs
into a folder for this marco to pick them up (it remames the five
files in those folders). It takes about 2 minutes for the macro from
start to finish so this means to get all the machines done I have to
sit at my computer and wait until its done and then manually dump five
more files into the folder.

So I think the easiest way to make this work is to get a macro that
can grab five files (unkonw names) and put them into a folder for me.
I need this macro to grab the files by date modified as I need the
oldest five files first.

Then once that is done I would like to loop my whole macro so it
continues to move the files in until all the datalogs are
completed......

CAN ANYONE HELP ME!!!!!

I have posted this a couple days ago but I haven't gotten any
responses.

 

[Tom Ogilvy]

Here is some untested code that may give you some ideas:

I didn't do anything with the code between the lines

=========== Your code ===========


=========== end of your code =======

except at the very bottom where I commented out your code that clears the
whole directory and adjusted it to only delete the 10 files that were used.
It assumes that the file names in .txt and .dat are the same for paired
files - differing only in the file extension



Sub P123_data_entry()
'
' P123_data_entry Macro
'
'
' Keyboard Shortcut: Ctrl+a
'ChDir "C:\Documents and Settings\owner\Desktop\Trending"
Dim s1 as String, s2 as String, sName as String
Dim i as long, j as Long, idex as Long, ii as Long
Dim vDat1A(), vDat1B(), vDat2A(), vDat2B()
Dim vDat()
s1 = "C:\Documents and Settings\owner\Desktop\Trending\P123\Known dat
files\"
s2 = "C:\Documents and Settings\owner\Desktop\Trending\P123\Known txt
files\"
sName = Dir(s1 & "*.*")
i = 0
Redim vDat1A(1 to 1)
Redim vDat1B(1 to 1)
Do While sName <> ""
i = i + 1
redim Preserve vDat1A(1 to i)
redim Preserve vDat1B(1 to i)
vDat1A(i) = Replace(lcase(sName),".dat","")
vDat1B(i) = fileDateTime(s1 & sName)
' Name s1 & sName As s1 & i & ".dat"
sName = Dir()
Loop

redim vDat2A(1 to Ubound(vDat1A))
redim vDat2B(1 to Ubound(vDat1A))

sName = Dir(s2 & "*.*")
i = 0
Do While sName <> ""
i = i + 1
vDat2A(i) = Replace(lcase(sName),".txt","")
vDat2B(i) = fileDateTime(s2 & sName)
' Name s2 & sName As s2 & i & ".txt"
sName = Dir()
Loop

Redim vDat(1 to ubound(vDat1A,1), 1 to 3)

for i = 1 to ubound(vDat,1)
sName = vDat1A(i)
idex = 0
for j = 1 to lbound(vDat2A)
if vDat2A(j) = vDat1a(i) then
idex = j
exit for
end if
Next j
vDat(i,1) = vDat1A(i)
vDat(i,2) = vDat2A(idex)
vDat(i,3) = vDat1B(i)
Next i
QuickSort vDat, 3, LBound(vDat, 1), UBound(vDat, 1), True

for i = 1 to ubound(vDat,1) Step 5
ii = 1
for j = i to i + 4
Name s1 & vDat(j,1) As s1 & ii & ".dat"
Name s2 & vDat(j,2) As s2 & ii & ".dat"
ii = ii + 1
Next j

'============== Your code ===========

ChDir _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files\1.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1))
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("Datalog1").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"1.txt"). _
Activate
Range("J7").Select
Application.CutCopyMode = False
ActiveWindow.Close
ChDir _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files\2.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1))
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("Datalog2").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"2.txt"). _
Activate
Range("K15").Select
Application.CutCopyMode = False
ActiveWindow.Close
ChDir _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files\3.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1))
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("Datalog3").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Windows( _
"3.txt"). _
Activate
Range("H15").Select
Application.CutCopyMode = False
ActiveWindow.Close
ChDir _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files\4.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1))
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("Datalog4").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"4.txt"). _
Activate
Range("J22").Select
Application.CutCopyMode = False
ActiveWindow.Close
ChDir _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
txt files\5.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("Datalog5").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"5.txt"). _
Activate
Range("K21").Select
Application.CutCopyMode = False
ActiveWindow.Close

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
dat files\1.dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1)
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("DAT1").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"1.dat"). _
Activate
Range("K21").Select
Application.CutCopyMode = False
ActiveWindow.Close
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
dat files\2.dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1)
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("DAT2").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"2.dat"). _
Activate
Range("K21").Select
Application.CutCopyMode = False
ActiveWindow.Close
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
dat files\3.dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1)
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("DAT3").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"3.dat"). _
Activate
Range("K21").Select
Application.CutCopyMode = False
ActiveWindow.Close
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
dat files\4.dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1)
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("DAT4").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"4.dat"). _
Activate
Range("K21").Select
Application.CutCopyMode = False
ActiveWindow.Close
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\owner\Desktop\Trending\P123\Known
dat files\5.dat", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
1)
Cells.Select
Selection.Copy
Windows("datalog info.xls").Activate
Sheets("DAT5").Select
Range("A1").Select
ActiveSheet.Paste
Windows( _
"5.dat"). _
Activate
Range("J19").Select
Application.CutCopyMode = False
ActiveWindow.Close
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Range("E5001").Select
Sheets("Info").Select
Range("A2:AC6").Select
Columns("U:U").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=5
Range("A2:Ag6").Select
Range("Ag2").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("B2").Select
Windows("P123 Trend Chart.xls").Activate
Windows("datalog info.xls").Activate
ActiveWindow.SmallScroll ToRight:=-23
Range("A2:Ag6").Select
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Rows("2:2").Select
Rows("2:6").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Selection.Interior.ColorIndex = xlNone
Range("B2").Select
Windows("datalog info.xls").Activate
Windows("test2.xls").Activate
Windows("datalog info.xls").Activate
Windows("datalog info.xls").Activate
Range("N6").Select
Application.CutCopyMode = False
Sheets("Sheet1").Select
Columns("N:O").Select
Selection.Copy
Windows("RORs.xls").Activate
Sheets("Sheet1").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, 2).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True
Sheets("Sheet2").Select
Range("A2").Select
Windows("datalog info.xls").Activate
Range("N6").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
Columns("N:O").Select
Selection.Copy
Windows("RORs.xls").Activate
Sheets("Sheet2").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, 2).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True
Sheets("Sheet3").Select
Range("A2").Select
Windows("datalog info.xls").Activate
Range("N5").Select
Application.CutCopyMode = False
Sheets("Sheet3").Select
Columns("N:O").Select
Selection.Copy
Windows("RORs.xls").Activate
Sheets("Sheet3").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False


Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, 2).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True
Range("a2").Select
Sheets("Sheet4").Select
Windows("test2.xls").Activate
Windows("datalog info.xls").Activate
Range("N16").Select
Application.CutCopyMode = False
Sheets("Sheet4").Select
Columns("N:O").Select
Selection.Copy
Windows("RORs.xls").Activate
Sheets("Sheet4").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, 2).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True
Range("D2").Select
Sheets("Sheet5").Select
Windows("test2.xls").Activate
Windows("datalog info.xls").Activate
Range("N16").Select
Application.CutCopyMode = False
Sheets("Sheet5").Select
Columns("N:O").Select
Selection.Copy
Windows("RORs.xls").Activate
Sheets("Sheet5").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, 2).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True




Range("A2").Select
Windows("datalog info.xls").Activate
Windows("P123 Trend Chart.xls").Activate
ActiveWindow.SmallScroll ToRight:=24
Windows("RORs.xls").Activate
Sheets("Sheet1").Select
Range("A2:B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AE7:AF7").Select
ActiveSheet.Paste
Windows("RORs.xls").Activate
Range("A3:B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AG7:AH7").Select
ActiveSheet.Paste
Range("AE8").Select
Windows("RORs.xls").Activate
Sheets("Sheet2").Select
Range("A2:B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AE8:AF8").Select
ActiveSheet.Paste
Range("AG8").Select
Windows("RORs.xls").Activate
Range("A3:B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AG8:AH8").Select
ActiveSheet.Paste
Range("AE9").Select
Windows("RORs.xls").Activate
Range("A4").Select
Application.CutCopyMode = False
Sheets("Sheet3").Select
Range("A2:B2").Select
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AE9:AF9").Select
ActiveSheet.Paste
Range("AG9").Select
Windows("RORs.xls").Activate
Range("A3:B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AG9:AH9").Select
ActiveSheet.Paste
Range("AE10").Select
Windows("RORs.xls").Activate
Range("A6").Select
Application.CutCopyMode = False
Sheets("Sheet4").Select
Range("A2:B2").Select
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AE10:AF10").Select
ActiveSheet.Paste
Range("AG10").Select
Windows("datalog info.xls").Activate
Windows("RORs.xls").Activate
Range("A3:B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("datalog info.xls").Activate
Windows("P123 Trend Chart.xls").Activate
Range("AG10:AH10").Select
ActiveSheet.Paste
Windows("RORs.xls").Activate
Range("D10").Select
Application.CutCopyMode = False
Sheets("Sheet5").Select
Range("A2:B2").Select
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AE11:AF11").Select
ActiveSheet.Paste
Range("AG11").Select
Windows("RORs.xls").Activate
Range("A3:B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("P123 Trend Chart.xls").Activate
Range("AG11:AH11").Select
ActiveSheet.Paste
Windows("RORs.xls").Activate
Range("A6").Select
Application.CutCopyMode = False
Windows("test2.xls").Activate
Windows("RORs.xls").Activate
Range("A2:B3").Select
Selection.Delete Shift:=xlUp
Sheets("Sheet4").Select
Range("A2:B3").Select
Selection.Delete Shift:=xlUp
Sheets("Sheet3").Select
Range("A2:B3").Select
Selection.Delete Shift:=xlUp
Sheets("Sheet2").Select
Range("A2:B3").Select
Selection.Delete Shift:=xlUp
Sheets("Sheet1").Select
Range("A2:B3").Select
Selection.Delete Shift:=xlUp
Range("C4").Select
Windows("P123 Trend Chart.xls").Activate
Windows("datalog info.xls").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Datalog1").Select
Selection.ClearContents
Sheets("Datalog2").Select
Selection.ClearContents
Sheets("Datalog3").Select
Selection.ClearContents
Sheets("Datalog4").Select
Selection.ClearContents
Sheets("Datalog5").Select
Selection.ClearContents
Sheets("DAT1").Select
Selection.ClearContents
Sheets("DAT2").Select
Selection.ClearContents
Sheets("DAT3").Select
Selection.ClearContents
Sheets("DAT4").Select
Selection.ClearContents
Sheets("DAT5").Select
Selection.ClearContents
Sheets("Datalog2").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Windows("test2.xls").Activate
On Error Resume Next
' Kill "C:\Documents and Settings\owner\Desktop\" & _
"Trending\P123\Known txt files\*.txt"
On Error GoTo 0
On Error Resume Next
' Kill "C:\Documents and Settings\owner\Desktop\" & _
"Trending\P123\Known dat files\*.dat"
On Error GoTo 0

'============== End of Your code
for j = 1 to 5
On Error Resume Next
kill s1 & j & ".dat"
kill s2 & j & ".txt"
On Error goto 0
Next j

Next i
End Sub

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to handle a a second dimension greater than 1 (or zero)
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm


i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) > X And i < R)
i = i + 1
Wend
While (X > SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub

 

[s_smith_iet]

Tom,

Thanks for your help
Now that it only deletes the 10 files (5 txt and 5 dat) I can keep all
of the files in those two folders with out transfering.
All of these files are on a server at work so I can get them droped
into those files instead.

The problem I am having know is the renaming.
You answered on of my first posts about remaming unknow files and the
macro renames the files 1-5000 (whatever amount I have in the folder)

here is the problem...... The first time I put in the files it works
then I delete the 5 files I have already used and when I go back to
rename them the second time it doesn't necessarily rename them 1-5 it
may go 1,4,6,8,9,23. It seems to be random which means that when my
macro searches for dat and txt files 1-5 it won't find them.

I am so close to perfecting this thing but at the same time I can't
see the light at the end of the tunnel.

Thanks for all your help

 

[dkinn]

I went a little different route, maybe you can find something useful here.
I pulled the list of files into a temp workbook and sorted on last date
modified and then take the 5 oldest and moved them to a second folder.

Good luck

david kinn


Sub CopyOldestFiles()

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\ATest")
Set tmp = Workbooks.Add

Set myfiles = f.Files

counter = 1
For Each fc In myfiles
tmp.Sheets(1).Cells(counter, 1).Value = fc.Name
tmp.Sheets(1).Cells(counter, 2).Value = fc.datelastmodified
counter = counter + 1
Next
tmp.Sheets(1).Columns("B:B").EntireColumn.AutoFit
tmp.Sheets(1).Range(Selection, Selection.End(xlToRight)).Select
tmp.Sheets(1).Range(Selection, Selection.End(xlDown)).Select
Set sortrange = Selection

tmp.Worksheets("Sheet1").Sort.SortFields.Clear
tmp.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With tmp.Worksheets("Sheet1").Sort
.SetRange sortrange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


For Count = 1 To 5

Set f2 = fso.GetFile("c:\ATest\" & tmp.Sheets(1).Cells(Count,
1).Value)
f2.Move ("C:\BTest\" & tmp.Sheets(1).Cells(Count, 1).Value)
Next Count

tmp.Close False

Set tmp = Nothing

End Sub

 

[Tom Ogilvy]

In one part, I see I didn't get eveything changed when I copied a line of
code

for i = 1 to ubound(vDat,1) Step 5
ii = 1
for j = i to i + 4
Name s1 & vDat(j,1) As s1 & ii & ".dat"
Name s2 & vDat(j,2) As s2 & ii & ".dat"
ii = ii + 1
Next j


should have had a .dat and a .txt


for i = 1 to ubound(vDat,1) Step 5
ii = 1
for j = i to i + 4
Name s1 & vDat(j,1) As s1 & ii & ".dat"
Name s2 & vDat(j,2) As s2 & ii & ".txt"
ii = ii + 1
Next j

I am not sure I follow all you are saying, but if you ran the first code I
gave you and changed all the file names to names like 1.dat and 1.txt, then
that could cause some duplicate filenames when combined with this code. I
saw this as handling the renaming. However, if you need to do the
renumbering first I would suggest you modify that original code to name the
files starting with an s1 & "A" & i & ".dat" as an example.


dkin also offers a workable approach for sorting the file names although I
suspect it is written for xl2007 and won't work (unchanged) in earlier
versions. It also doesn't implement 5 at a time or explicitly address the
2nd set of files. So you would still need to tie it all together.

 

[s_smith_iet]

David

very nice......
It works great That was the last peice of the puzzle
Now if I can loop the entire macro I can automate the entire process.

 

[s_smith_iet]

stupid question
but how can I loop it
I want a message box to come up when its done