Solved

open CSV file with Excel 2010 where date changes every day

Posted on 2013-05-22
5
374 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
[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
  • 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
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…

726 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