Solved

Open latest file

Posted on 2011-09-30
11
190 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

747 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

11 Experts available now in Live!

Get 1:1 Help Now