pulling data from a csv file based on specific dates

[spittingfire]

Hi All,

I'm in search of some modifications to a code that was given to by mdmackillop - below is the url to the solution that was provided to me.

http://www.vbaexpress.com/forum/show...ased-on-a-date


The situation now is that the data file has now changed from an xlsb extension to a csv extension.

The other thing that has changed is the layout of the data however it may be easier to work with.

What I need is data copied from the csv data file from 7 days ago (today -7) and also the previous day (today -1).

For example if today is May 30th I will like the data copied from May 23rd (g2:x49) and pasted into the forecast_test.xlsm sheet in the "IDP- last week" sheet "B2:S49".
Then I will like it to do the say for May 29th (previous day) and in this case (g290:x337) to forecast_test.xlsm sheet in the "IDP- yesterday" sheet "B2:S49".

What I've learnt is that every day a new file with the same file name is generated and the very first day on the top of the file will be 7 days ago which means that the location of the previous day will also be static as well, however maybe it may be best if you can make use of the date "START_TIME" in column C.

In this case the file name is a static name and will be over-written daily.

Any help will be appreciated.

Thanks

[mdmackillop]

Option Explicit


Sub Test()




    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim ws As Worksheet
    Dim Dte As Long, i As Long, x As Long
    Dim r As Range
    Dim FName As String
    Dim Days, Shts
    Dim rw As Long




    Set wbTarget = ThisWorkbook
    FName = "28Day_CXIE.csv"
    Set wbSource = Workbooks(FName)
    Set ws = wbSource.Sheets("28Day_CXIE")

    Days = Array(1, 7)
    Shts = Array("IDP- yesterday", "IDP- last week")
    For i = 0 To 1
        x = Days(i)
        ws.Range("$C:$C").AutoFilter Field:=1, Criteria1:=Format(Date - x, "m/d/yyyy")
        rw = Cells(Rows.Count, 3).End(xlUp).Row
        Set r = ws.Range(Cells(2, 7), Cells(rw, "X")).SpecialCells(xlCellTypeVisible)
        wbTarget.Sheets(Shts(i)).Range("B2").Resize(r.Rows.Count, 18).Value = r.Value
    Next i
End Sub

[spittingfire]

Thanks for the assistance again mdmackillop. Where will I insert the path for the file name?

The path where the file sits is "\\rsoesnewfmu\AutorunJobs\National\21Report\35DayForecast\"

Thanks

[mdmackillop]

Set wbSource = Workbooks.open("\\rsoesnewfmu\AutorunJobs\National\21Report\35DayForecast\28Day_CXIE.csv"

[spittingfire]

Awesome!! Thanks again for all your help mdmackillop - it works as expected.