Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 2 files joined together
I have a file "C:/temp"
I have files in this folder with only 1 worksheet in each workbook. There are many file in this folder, however I want my sub to take the last 2 files to have been saved to this folder and copy them to be 2 worksheets in a separate workbook. Possible? TIA - Mike *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 2 files joined together
Hi, See VB "FileDateTime" function which will give you the info to find latest modified date ... Example from VB Help: Dim MyStamp ' Assume TESTFILE was last modified on February 12, 1993 at 4:35:47 PM. ' Assume English/U.S. locale settings. MyStamp = FileDateTime("TESTFILE") ' Returns "2/12/93 4:35:47 PM". "Michael Smith" wrote: I have a file "C:/temp" I have files in this folder with only 1 worksheet in each workbook. There are many file in this folder, however I want my sub to take the last 2 files to have been saved to this folder and copy them to be 2 worksheets in a separate workbook. Possible? TIA - Mike *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 2 files joined together
Great that helps....how would i then compile a list of all times and
file names that are in a folder? Would this be a For Each loop? *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last 2 files joined together
Mike,
Try this: it list the files and date/times in columns A/B and sorts them in descending order so that rows 1 & 2 are the latest. Place this code in a general module in your workbook - it uses first worksheet (normally Sheet1) to store the results. Change the "With Worksheet.." statement to suit AND the .Lookin statement . Execute the FindFile macro. HTH Sub FindFile() Dim FNames() As String, FTimes() As Date Set fs = Application.FileSearch With fs filescount = 0 .LookIn = "C:\Documents and Settings\John\My Documents\My Excel" '<=== CHANGE .Filename = "*.xls" .Execute If .FoundFiles.Count 0 Then For i = 1 To .FoundFiles.Count docname = FileNameOnly(.FoundFiles(i)) filescount = filescount + 1 ReDim Preserve FNames(filescount) ReDim Preserve FTimes(filescount) FNames(filescount) = docname FTimes(filescount) = FileDateTime(.FoundFiles(i)) Next i End If End With With Worksheets(1) For i = 1 To filescount .Cells(i, 1) = FTimes(i) .Cells(i, 2) = FNames(i) Next .Range("a1:b" & filescount).Sort Key1:=.Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End Sub Function FileNameOnly(pname) As String ' Returns the filename from a path/filename string Dim i As Integer, length As Integer, temp As String length = Len(pname) temp = "" For i = length To 1 Step -1 If Mid(pname, i, 1) = Application.PathSeparator Then FileNameOnly = temp Exit Function End If temp = Mid(pname, i, 1) & temp Next i FileNameOnly = pname End Function "Michael Smith" wrote: Great that helps....how would i then compile a list of all times and file names that are in a folder? Would this be a For Each loop? *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lines from chart are not joined | Charts and Charting in Excel | |||
bar graphs that are have bars joined together | New Users to Excel | |||
2D Points Joined by Vectors | Charts and Charting in Excel | |||
Express 'Joined up' IF's Q | Excel Worksheet Functions | |||
Need to unhide 2 columns that I joined together - Help! | Excel Discussion (Misc queries) |