Seamus2626
asked on
Adjust Code
I have attached a nifty piece of code that Rory kindly supplied me. It opens the latest file in the prescribed fodlers.
Can i take this one stage further and ask it to bring me to a tab within that file which is going to be Very Hidden. The tab will be called Check
Thanks
Seamus
Can i take this one stage further and ask it to bring me to a tab within that file which is going to be Very Hidden. The tab will be called Check
Thanks
Seamus
Sub OpenLatestFile()
Dim initPath As String, Direc As String, strFile As String, strFinalFile As String
Dim DT As Date, dteFile As Date
Dim objFSO, objFdr, objSubFdr
' Change Path of parent directory here
' Don't forget the "\" after the path
initPath = "\\ukhibmdata02\rights\Asset Services MI\Merit Payable & Receivables\2011\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFdr = objFSO.getfolder(initPath)
' loop through folders, checking names
For Each objSubFdr In objFdr.subfolders
If IsDate(objSubFdr.Name) Then
If DT = #12:00:00 AM# Then
DT = CDate(objSubFdr.Name)
Direc = objSubFdr.Name
Else
If CDate(objSubFdr.Name) > DT Then
DT = CDate(objSubFdr.Name)
Direc = objSubFdr.Name
End If
End If
End If
Next objSubFdr
' check we found a date
If Len(Trim(Direc)) <> 0 Then
' now need to loop through the files and find the last one
' assumes file names are like "09022011 Merit.xls"
' so we want the first 8 characters converted to a date
strFile = Dir(initPath & Direc & "\*.xls")
If strFile <> "" Then
Do
If dteFile < GetDateFromFileName(strFile) Then
strFinalFile = strFile
strFile = Dir
End If
Loop While strFile <> ""
If Len(strFinalFile) > 0 Then Workbooks.Open initPath & Direc & "\" & strFinalFile
Else
MsgBox "No workbooks in " & initPath & Direc & "\"
End If
Else
MsgBox "No date files found"
End If
End Sub
Function GetDateFromFileName(strFile As String) As Date
' returns date from last 8 characters of file name
' assumes ddmmyyyy format
Dim dteTemp As Date, strTemp As String
strTemp = Right$(Replace$(strFile, ".xls", "", , , vbTextCompare), 8)
If Len(strTemp) < 8 Then Exit Function
strTemp = Left$(strTemp, 2) & "/" & Mid$(strTemp, 3, 2) & "/" & Mid$(strTemp, 5, 4)
If IsDate(strTemp) Then GetDateFromFileName = CDate(strTemp)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers
Seamus