Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Open latest file

Posted on 2011-09-30
11
Medium Priority
?
230 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

715 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