Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
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 -- Regards, Tom Ogilvy wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
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 " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
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. -- Regards, Tom Ogilvy wrote in message ups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP!!!!
stupid question
but how can I loop it I want a message box to come up when its done |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|