Pulling in data from CSV file with changing file name

Hi all,

I'm using Excel 2010 and trying to write a macro to automatically 'pull' data from 4 different .CSV files into 4 different tabs of another Excel file every day. Key points are:

1) The 4 CSV files are kept in a folder with 100+ other CSV files. Every day 4 new CSV files are dropped into the folder, they are identifiable by their file name. As an example, one of them for 22 February 2012 is called "20120222.TEXSECPOS.GRPADELP.142617117".

2) The 9 digit number at the end of each file name is random and changes each day. The first part is the date, this also changes each day. The two sections in between remain the same each day.

3) I'm new to programming in VBA so all I've done so far is record the following Macro for one of the files. The problem is I don't know how to adjust the macro so that it will automatically look for the file name with yesterday's date (workday) at the start. It would also have to igore the fact that the last 9 digits of the file name change randomly each day. Can anyone please help?

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
Workbooks.Open Filename:= _
"F:\PB\PB Data Sheets\20120222.TEXSECPOS.GRPADELP.142617117.CSV"
Cells.Select
Selection.Copy
Windows("Copy data sheet test.xlsm").Activate
Cells.Select
ActiveSheet.Paste
Windows("20120222.TEXSECPOS.GRPADELP.142617117.CSV").Activate
Application.CutCopyMode = False
ActiveWindow.Close
Range("A1").Select
End Sub

This code shows how to loop through yesterday's CSV files with file names as described.

Please Login or Register  to view this content.

See if you can incorporate your code into the loop - your code would replace the MsgBox statement.

Thanks for such a neat solution. I was able to tweak it a bit but I'm not sure how to loop it for the other 3 worksheets and also how to close the dynamically named worksheet. The code I've got so far is below:

Sub Loop_CSV_files()

Dim folderPath As String
Dim fileName As String
Dim yesterday As String

'Folder containing the .csv files

folderPath = "F:\PB\PB Data Sheets"

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

yesterday = Format(Date - 1, "yyyymmdd")
fileName = Dir(folderPath & yesterday & ".TEXSECPOS.GRPADELP.*.CSV")

Do While fileName <> ""

ChDir "F:\PB\PB Data Sheets"
Workbooks.Open fileName:= _
Dir(folderPath & yesterday & ".TEXSECPOS.GRPADELP.*.CSV")
Cells.Select
Selection.Copy
Windows("PB Data Sheet Copy Maco Test.xlsm").Activate
Cells.Select
ActiveSheet.Paste


End
Loop
MsgBox "Finished"

End Sub

Use the tags around the code

Originally Posted by adelcap

'pull' data from 4 different .CSV files into 4 different tabs of another Excel file

Into which 4 tabs (sheets) should the 4 .csv files be placed? I can see from your first code (please surround code with [ code] [/ code] tags without the spaces) that the data from one .csv file replaces the cells on the active sheet, but does this active sheet have a particular name, or index in the list of sheets (e.g. is it the first, second, last sheet etc.)? Into which sheet should each of the other 3 .csv files be placed? Does it replace the existing data, or go below or to the side, etc.

You haven't provided enough information for your request and I need exact answers to these questions before I can develop the code to do what you want.

With the further explanation, the Dir file loop which I first thought would do the job isn't suitable because each .csv file needs to be copied to a specific sheet. Try this instead - the code should be placed in a standard module in your master workbook.

Please Login or Register  to view this content.

That seems to work very nicely. Thanks Chippy!

Do you mean if run on a Monday it should look for Friday's files? And if run on a Tuesday and Monday is a bank holiday it should also look for Friday's files?

Yes, exactly, where the following are the bank holidays it should exclude for 2012:


26/11/2012
27/12/2011
02/01/2012
19/03/2012
06/04/2012
09/04/2012
07/05/2012
04/06/2012
05/06/2012
12/07/2012
27/08/2012
25/12/2012

Try this code, which replaces the previous code.

Please Login or Register  to view this content.

As written above, the holiday dates are expected to be in cells A2:A10 on the Holidays sheet, or you could hard-code and use them like this:

Please Login or Register  to view this content.

This works great apart from the bank holidays. I've created a new sheet titled 'Holidays' and added in 28/02/2012 in A1 as a test. It still seems to pull the CSV from 28/02/2012..

With the single holiday date of 28/02/2012 in A1, did you change the following reference to it?

Please Login or Register  to view this content.

The above works for me. Run today, it looks for files with 20120227 in the file name.

Did you try the array way of specifying the holidays, as shown?

I've used the range and it seems to be working perfectly now, thanks!

Finally, is there a way to leave Excel open and have the macro run each weekday morning at say 7am?

Have a look at Application.OnTime - http://www.cpearson.com/excel/OnTime.aspx.

But what if the weekday was a bank holiday? Presumably you wouldn't want the macro to run then, but on the next proper working day? All this could be handled by the procedure called by OnTime.

See http://www.rondebruin.nl/mail/prevent.htm

This is great, now works perfectly. Thanks Chippy

Originally Posted by adelcap

the date format switches on the 13th day.

This is a common problem with British dates in .csv files. Excel interprets them as USA dates. Fortunately the solution is simple. Change the Workbooks.Open line to:

Please Login or Register  to view this content.

I can't even see where the paste command is

The Cells.Copy line specifies a Destination argument (see 'Copy method as it applies to the Range object' in the VB Help), so it doesn't paste separately (via the clipboard) but copies the data in one go. Because of this the Application.CutCopyMode = False line is unnecessary and you can delete that line.

Chippy, thank you! Genius.

One further question, the current code copies only the front sheet from 'File A' to a specificed sheet in 'File B'.

Is there a way of copying more than one sheet from 'File A' to more than one specificed sheet in 'File B'?

e.g. File A has 5 sheets, labelled Sheet 1 through to Sheet 5.

I'd like the VBA code to copy each of the sheets to specified destination sheets in 'File B'?