Solved

open CSV file with Excel 2010 where date changes every day

Posted on 2013-05-22
5
369 Views
Last Modified: 2013-05-24
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
Comment
Question by:Jagwarman
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 39188207
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 39188316
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
 

Author Comment

by:Jagwarman
ID: 39190014
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
 

Author Closing Comment

by:Jagwarman
ID: 39193455
Thanks
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39195718
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Excel export not behaving 2 25
Most Consistent Performer 4 21
Pivot help - Display only Is Not Null 7 16
Problem with Excel and File Size 7 33
This article will show you how to use shortcut menus in the Access run-time environment.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 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

15 Experts available now in Live!

Get 1:1 Help Now