Solved

open CSV file with Excel 2010 where date changes every day

Posted on 2013-05-22
5
364 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

11 Experts available now in Live!

Get 1:1 Help Now