?
Solved

Amend code

Posted on 2011-05-12
8
Medium Priority
?
255 Views
Last Modified: 2012-05-11
Hi,

I have some code that opens the latest file from a specified folder (with various months e.g. Mar,Apr,May,June etc)

Can this code be amended so that it opens the latest file from the previous months folder. So if it was ran today it would open the latest xls file from April.

Thanks
Seamus
ub 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\S-gdata\EVERYONE\Ops to PC folders\Div Rec Email\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
           Sheets("Current breaks").Visible = True
           Sheets("Current breaks").Select
         End If
         
         
      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

Open in new window

0
Comment
Question by:Seamus2626
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35744888
I don't know how you folders are named but if they are named like "Jan", "Feb", etc, you just need to change this like:

initPath = "\\ukhibmdata02\S-gdata\EVERYONE\Ops to PC folders\Div Rec Email\2011\"

to this:

initPath = "\\ukhibmdata02\S-gdata\EVERYONE\Ops to PC folders\Div Rec Email\2011\" & Format(Month(Now()), "mmm")

jppinto
0
 

Author Comment

by:Seamus2626
ID: 35744900
Hey jppinto, the folders are named

03 Mar, 04 Apr, 05 May etc

(For some reason, the number of the month is put before the text!)
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35744907
So just change to this:

initPath = "\\ukhibmdata02\S-gdata\EVERYONE\Ops to PC folders\Div Rec Email\2011\" & Format(Month(Now()), "dd") & " " & Format(Month(Now()), "mmm")

it will give you folder names like

12 May
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35744909
Try this:
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\S-gdata\EVERYONE\Ops to PC folders\Div Rec Email\2011\"
   
   Direc = Format(Date - Day(Date), "mm mmm")
   
   ' check we found a folder
   If Len(Dir(initPath & Direc, vbDirectory)) <> 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
           Sheets("Current breaks").Visible = True
           Sheets("Current breaks").Select
         End If
         
         
      Else
         MsgBox "No workbooks in " & initPath & Direc & "\"
      End If
   Else
      MsgBox "No date files found"
   End If
End Sub

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 35744936
Thanks Jppinto but i got errors on that code, it couldnt find the Initial Path

Thanks Rory that worked perfectly

Cheers,
Seamus
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35744944
Replace lines 17-27 with

      If IsDate(objSubFdr.Name) Then
      If Format(CDate(objSubFdr.Name), "mmmyyyy") = _
        Format(DateSerial(Year(Date), Month(Date), 1), "mmmyyyy") 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
      End If
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35744946
OOPs accepted already
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35744963
@jppinto,
The key is the weird naming convention for Seamus' folders - it's always the month number and name - i.e. 05 MAY is month 5, not day 5, so there's only one folder per month.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question