Solved

Open latest file

Posted on 2011-09-30
11
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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
 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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 describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 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