Solved

Open latest file

Posted on 2011-09-30
11
221 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
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!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

752 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