Copy row of data from daily csv to Master file

[lindomsm]

Hi All,

I'm pretty new to VBA so any help is much appreciated.

I have an ftp folder to which a new csv file is uploaded every day with one row of headings in row 1, and 1 row of data in row 2 (Columns A-O). Each filename contains a date stamp in the form of 2013-06-14_DailyData.

I need to write a macro that will copy the row of data every day and add it beneath the last row of data in the Master file. Column A in both the daily csv and master file contain the date in the same format.

Any ideas on how to do this?

Thanks,

Ryan

[fiseboy]

Top, also have this issue

[lindomsm]

This has done the job for me!

Sub getdata()


Const ForReading = 1, ForWriting = 2, ForAppending = 8 'Declaring constants
Dim FSO, MyFile, FileName, TextLine
Dim nextdate As Date
sheettopastein = "Master"

currow = 1
Do Until ActiveWorkbook.Sheets(sheettopastein).Cells(currow, 2).Value = "" 'Start at row 1, search down until Column B = "" / Blank
currow = currow + 1
Loop

nextdate = ActiveWorkbook.Sheets(sheettopastein).Cells(currow, 1).Value

sFileloc = "\\C:\folder\" ' Select the file location

sFilename = Format(nextdate, "yyyy-mm-dd") + "_counts.csv" ' Select which file you want to open for output

fullfile = sFileloc + sFilename ' The file = the location + the filename

Set FSO = CreateObject("Scripting.FileSystemObject") 'Creating the reference at run-time to allow us to manipulate a file
Set MyFile = FSO.OpenTextFile(fullfile, ForReading) ' Set the Myfile identifier for the open file and open it for reading only

MyFile.SkipLine ' skip a given number of lines starting from the current position within the file.

Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine

Loop



currow = 1
Do Until ActiveWorkbook.Sheets(sheettopastein).Cells(currow, 1).Value = nextdate
currow = currow + 1
Loop

currcol = 1

ActiveWorkbook.Sheets(sheettopastein).Cells(currow, currcol).Value = TextLine

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Cells(currow, currcol).TextToColumns _
Destination:=ActiveWorkbook.Sheets(sheettopastein).Cells(currow, currcol), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Dim celldate As Date

celldate = Cells(currow, currcol).Value
Cells(currow, currcol).Value = celldate


Application.ScreenUpdating = True
Application.DisplayAlerts = True

MyFile.Close



End Sub

[snb]

Please, use code tags !!

You could try to use VBA :

Please Login or Register  to view this content.

You only have to adapt the foldername "G:\OF\"