?
Solved

open CSV file with Excel 2010 where date changes every day

Posted on 2013-05-22
5
Medium Priority
?
381 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 1500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

765 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