PDA

View Full Version : [SOLVED] pulling data from a csv file based on specific dates



spittingfire
05-30-2016, 06:50 PM
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/showthread.php?56112-vba-to-loop-through-multiple-worksheets-and-paste-specific-data-based-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
05-31-2016, 11:31 AM
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
05-31-2016, 11:53 AM
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
05-31-2016, 11:58 AM
Set wbSource = Workbooks.open("\\rsoesnewfmu\AutorunJobs\National\21Report\35DayForecast\28Day_CXIE.csv"

spittingfire
05-31-2016, 03:41 PM
Awesome!! Thanks again for all your help mdmackillop - it works as expected.