Excel Automation For reading data from .csv files in a folder

Here is what I want to accomplish. I have no coding experience and would like someone to help me in completing this task, please !

Everyday (at 0800/1200/1700 Hrs) I receive reports in .csv format and when I open the files in Excel, they are neatly formatted with the data but the data is huge. The information I wanted is under E2:F14 range. Also the file I receive will have the name in the format: current_201106070800.csv (current_YYYYMMDDHHHH.csv). I have these files for the past 6 months so mining the data is not a option for me.

Now, I would like to gather the information from all the reports I receive and house in a master excel sheet (A Master Data Template) with a macro which can:

- Scan the folder for new files and if there are any new files found grab the information (from the range E2:F14 range) from the new file and copy to the master excel file. (This is where I need help! )

I'm looking for a output in the master excel sheet something like this:

-----A------------B--------------------------C
1---Period------201106070800-------201106071200
2---Data1-------7890--------------------89098
3---Data 2------890878-----------------94580
4---Data 3------898090-----------------902850928

So, B1 is the file name from where the data is extracted, B2:B4 is the data copied from the file to master excel sheet. Same goes for C1:C4

I can manually copy the files everyday from my email to the folder and open the master excel sheet and run the macro to receive the data.

The next tasks I will be able to complete by myself, to refine the data further and generate pivot tables/reports and stuff.

Can someone please share the code if already available. I would appreciate any help in complete the macro task.

Thanks in advance. Have a nice day.

Hello nalayak21,

Welcome to the Forum!

It would be very helpful to see a copy of the csv file. You should zip the file before you post it.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Hello nalayak21,

Thanks for the sample file. Are the files laid out the same using just columns "A" and "B"?

Hello nalayak21,

Here is the macro. You will need to change the folder where the files are stored. It is marked in bold text. Each file will be added the "Master" sheet starting with the next empty column to the right. Borders are added around each group of cells after they are copied over. The file is chosen by the latest modification date. This ensures the most up to date is used.

Please Login or Register  to view this content.

Hello nalayak21,

I downloaded the files, changed the directory path in the macro to point to files, and it ran with no errors. Unfortunately, error 400 in Windows is a general error. To get any meaningful information, you will need to debug the macro by stepping through it line by line. Once you have located the problem line of code, I can help you fix it.

Hello nlayak21,

Let's formalize the Open statement more instead of relying on defaults.

Change the CSVwkb open statement to this...

Please Login or Register  to view this content.