Solved

Open latest file

Posted on 2011-09-30
11
197 Views
Last Modified: 2012-05-12
Hi,

Attached is some code i have to open the latest file from a folder.

I need an amendment though. The code currently has a function to trim the mid section to get the date from it

  'Name Format Coupons 610 - 13 APRIL 2011.xls

I want to use this code on another file path, but the files in that folder are formatted as

Sophis Fiscal 26092011.xls

Can anyone amend the function so it cuts the above to 26092011.xls and then read the date from there.

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 strFolder As String
   Dim objFSO, objFdr, objSubFdr
   
   
   ' Change Path of parent directory here
   ' Don't forget the "\" after the path
   
   Application.DisplayAlerts = False

   
   initPath = "\\ukhibmdata02\Rights\Asset Services MI\Sophis Fiscal breaks\2011\"
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFdr = objFSO.getfolder(initPath)
   
   dteFile = GetNextWorkday(Date, 1) 'Tomorrow workday

  Direc = Format(dteFile, "m mmm yyyy")
  
      strfile = Dir(initPath & Direc & "\*.xls")
      If strfile <> "" Then
         Do
            If dteFile <> GetDateFromFileName(strfile) Then
               strFinalFile = strfile
               strfile = Dir
            Else
               strFinalFile = strfile
               Exit Do
            End If
         Loop While strfile <> ""

         If Len(strFinalFile) > 0 Then
           Workbooks.Open initPath & Direc & "\" & strFinalFile
           Sheets("Sheet1").Visible = True
           Sheets("Sheet1").Select
         End If
         
         
      Else
         MsgBox "No workbooks in " & initPath & Direc & "\"
      End If

   
   With ActiveWindow
    Application.DisplayAlerts = False
  
   
   End With
   

   
   
 

End Sub


Function GetDateFromFileName(ByVal strfile As String) As Date

    'Name Format Coupons 610 - 13 APRIL 2011.xls
    If InStr(strfile, "-") <> 0 Then
        GetDateFromFileName = Trim(Mid(strfile, InStr(strfile, "-") + 1, Len(strfile) - InStr(strfile, "-") - 4))
   
    End If
End Function

Function GetNextWorkday(dteFrom As Date, lngCount As Long) As Date
    Dim dteTemp As Date
    dteTemp = dteFrom + lngCount
    Select Case Weekday(dteTemp, 2)
        Case 1 To 5
            GetNextWorkday = dteTemp
        Case 6
            GetNextWorkday = dteTemp - 2
        Case 7
            GetNextWorkday = dteTemp - 1
    End Select
End Function

Open in new window

0
Comment
Question by:Seamus2626
  • 6
  • 5
11 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36890767
Change the GetDateFromFilename function to:
Function GetDateFromFileName(ByVal strFile As String) As Date
   Dim varData
   Dim strDate As String
    'Name Format Coupons 610 - 13 APRIL 2011.xls
    If InStr(strFile, " ") <> 0 Then
      varData = Split(strFile, " ")
      strDate = varData(UBound(varData))
      strDate = Left$(strDate, InStr(strDate, ".") - 1)
      
        GetDateFromFileName = DateValue(Format(CLng(strDate), "00-00-0000"))
    End If
End Function

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 36890778
Thanks Rory, that gave me a type mismatch on line


        GetDateFromFileName = DateValue(Format(CLng(strDate), "00-00-0000"))

Seamus
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36890790
Then you did not pass "Sophis Fiscal 26092011.xls" to it, I suspect.
0
 

Author Comment

by:Seamus2626
ID: 36890816
What part of the code would i pass Sophis Fiscal 26092011?

Cheers,
Seamus
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36890859
Your code passes filenames in a loop, so let's try a more defensively coded version:
Function GetDateFromFileName(ByVal strFile As String) As Date
   Dim varData
   Dim strDate           As String
   'Expected name Format: "Sophis Fiscal 26092011.xls"
   If InStr(strFile, " ") <> 0 Then
      varData = Split(strFile, " ")
      strDate = varData(UBound(varData))
      strDate = Left$(strDate, InStr(strDate, ".") - 1)
      If IsNumeric(strDate) Then
         strDate = Format(CLng(strDate), "00-00-0000")
         If IsDate(strDate) Then GetDateFromFileName = CDate(strDate)
      End If
   End If
End Function

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:Seamus2626
ID: 36890886
Your a legend Rory, thank you very much, that has worked a treat!

Cheers,
Seamus
0
 

Author Comment

by:Seamus2626
ID: 36890983
One question Rory, if i wanted to change this from opening a file minus 1 day to opening a file minus  2 days, what part of the code do i change to say -2 days? So today i would open up 28/09/2011

Thanks
Seamus
Function GetNextWorkday(dteFrom As Date, lngCount As Long) As Date
    Dim dteTemp As Date
    dteTemp = dteFrom + lngCount
    Select Case Weekday(dteTemp, 2)
        Case 1 To 5
            GetNextWorkday = dteTemp
        Case 6
            GetNextWorkday = dteTemp - 2
        Case 7
            GetNextWorkday = dteTemp - 1
    End Select
End Function

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36890996
You don't change that function. When you call it, you specify a start date and how many days to offset. So just change the offset from -1 to -2 or whatever you like.
0
 

Author Comment

by:Seamus2626
ID: 36891027
Thats what i thought and i changed the below to -2, but it still opens the file 29092011?
Function GetDateFromFileName(ByVal strFile As String) As Date
   Dim varData
   Dim strDate           As String
   'Expected name Format: "Sophis Fiscal 26092011.xls"
   If InStr(strFile, " ") <> 0 Then
      varData = Split(strFile, " ")
      strDate = varData(UBound(varData))
      strDate = Left$(strDate, InStr(strDate, ".") - 2)
      If IsNumeric(strDate) Then
         strDate = Format(CLng(strDate), "00-00-0000")
         If IsDate(strDate) Then GetDateFromFileName = CDate(strDate)
      End If
   End If
End Function

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36891043
That code is not calling the function you just posted.

In your initial sub you have:
dteFile = GetNextWorkday(Date, 1) 'Tomorrow workday

Open in new window


change the 1 to 2.
0
 

Author Comment

by:Seamus2626
ID: 36891075
Thank you!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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.

943 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now