Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Workbook in A Folder
Hi,
Which command do I use to locate the last workbook in A Folder so e.g. My Documents So Far I have sub fold() dim i i = C:\My Documents\Workbooks.Count MsgBox C:\My Documents\Workbooks(i) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Workbook in A Folder
If it is in a folder and not open in Excel, then it will not be in the
workbooks collection. Define what makes a file the last workbook in a folder. Alphabetically, by the date it was saved, ?? -- Regards, Tom Ogilvy "teresa" wrote in message ... Hi, Which command do I use to locate the last workbook in A Folder so e.g. My Documents So Far I have sub fold() dim i i = C:\My Documents\Workbooks.Count MsgBox C:\My Documents\Workbooks(i) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Workbook in A Folder
What do you mean by "last workbook": based on create date, modified
date, filename.... ? Tim. "teresa" wrote in message ... Hi, Which command do I use to locate the last workbook in A Folder so e.g. My Documents So Far I have sub fold() dim i i = C:\My Documents\Workbooks.Count MsgBox C:\My Documents\Workbooks(i) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Workbook in A Folder
Teresa, this is procedure Dana DeLouis posted that I have modified a
little for my needs. It finds the 'Newest' or last file in a folder. '################################################# ####### Public Sub OpenNewestFile(searchDir$) Dim file20Dir$ '// Modified--Origionally posted by Dana DeLouis '// The first search is a workaround for an excel bug '// in order to actually do the search you want With Application.FileSearch '// Wake Excel up for msoSortByLastModified to work ! ..NewSearch ..LookIn = "C:\" ..FileName = "*.jnk" ..Execute SortBy:=msoSortBySize End With With Application.FileSearch ..NewSearch 'searchDir must be passed to the Sub ..LookIn = searchDir ..SearchSubFolders = False ..FileType = msoFileTypeExcelWorkbooks If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) 0 Then Dim nameArr As Variant Dim myVar$, myFileName$ myVar = .FoundFiles(1) nameArr = Evaluate("{""" & Application.Substitute( _ myVar, "\", """,""") & """}") myFileName = nameArr(UBound(nameArr)) Response = MsgBox("The newest 20 file is " & myFileName & " created " _ & FileDateTime(.FoundFiles(1)) & ";Click OK to open; Click cancel to select correct 20 file.", vbOKCancel) If Response < 2 Then Workbooks.Open .FoundFiles(1) Exit Sub Else file20Dir = "" MsgBox "Select the 'Current' download file." On Error GoTo 0 file20Dir = Application.GetOpenFilename If file20Dir = "False" Then MsgBox "You failed to select the 20 file. This will end the process.", vbOKOnly Exit Sub End If Call fOpen(file20Dir) End If Else MsgBox "There was no 20 file found, select the 'Current' 20 file." file20Dir = "" MsgBox "Select the Latest '20 file'." On Error GoTo 0 file20Dir = Application.GetOpenFilename If file20Dir = "False" Then MsgBox "You failed to select the 20 file. This will end the process.", vbOKOnly Exit Sub End If Call fOpen(file20Dir) End If End With End Sub '################################################# ####### |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook saved in FTP folder | Excel Discussion (Misc queries) | |||
Link with workbook in other folder which is in another group | Excel Discussion (Misc queries) | |||
for every workbook in folder | Excel Programming | |||
Do same thing to each workbook found in a folder | Excel Programming | |||
New Folder, Workbook, and Worksheet | Excel Programming |