• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

open CSV file with Excel 2010 where date changes every day

Could someone provide me with some code that will allow me to open a csv file with Excel 2010 where the date changes every day.  The report name [today] is

FinanceReport_20130521_22171100_csv

Yesterdays report was FinanceReport_20130520_22174900_csv

Previous day was FinanceReport_20130517_22174700_csv

Each day I need to open the last working days file so today I need to open the file dated 20130521 but on a Monday I need to open the file from the Friday so this last Monday I needed to open the file with the date 20130517.

This is a tricky one so Thanks in advance.
0
Jagwarman
Asked:
Jagwarman
  • 3
  • 2
1 Solution
 
rspahitzCommented:
There's a few ways to handle this.  One way is to simply look for the file with the most recent timestamp.  Another way is to look for the file with a date just prior to today (yesterday, but if not that then the day before, etc.)

Do you have a preference on either one?  Will there ever be two files on the same day?  Will one every have an older date but come later?

Let me see what I can come up with....
0
 
rspahitzCommented:
This code should allow you to get the newest file in a predefined file path (excluding the file name).

Sub FindNewestFile()
    MsgBox "The newest file is " & GetLastFile("C:\")
End Sub

Function GetLastFile(Path As String) As String
    On Error GoTo GetLastFile_Err
    
    Dim strFileName As String
    Dim strNewestFile As String
    Dim datNewestTime As Date
    
    strFileName = Dir(Path)
    strNewestFile = ""
    datNewestTime = 0
    Do Until strFileName = ""
        If FileDateTime(Path & strFileName) > datNewestTime Then
            strNewestFile = strFileName
            datNewestTime = FileDateTime(Path & strNewestFile)
        End If
        strFileName = Dir
    Loop
    
GetLastFile_Exit:
    GetLastFile = Path & strNewestFile
    Exit Function
    
GetLastFile_Err:
    strFileName = "{Error getting file: " & Err.Description & "}"
    Resume GetLastFile_Exit
End Function

Open in new window


From there you could get Excel to open the file like this:
    Workbooks.Open GetLastFile("C:\")

Open in new window


You may also want to add validation to ensure that the latest file is a cvs, etc.
0
 
JagwarmanAuthor Commented:
There is only one file each day. I don't think your code is what I am looking for. I need the code to just open the file. When I run your code it send out a message "The newest file is........"

If it helps, the file path is H:\live\RecTool\From_RS

Thanks
0
 
JagwarmanAuthor Commented:
Thanks
0
 
rspahitzCommented:
The point of the above code was to verify that it was getting the correct file.  That was your first requirement.
As I showed in the little block below that (which maybe you missed), next part was to open the file:

Sub FindNewestFile()
    'MsgBox "The newest file is " & GetLastFile("C:\")
    Workbooks.Open GetLastFile("H:\live\RecTool\From_RS")
End Sub

Open in new window


See if that handles it.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now