Export From notepad to Excel

[DaddyH]

Hi guys, please could you help save me some time.

This is basically what i have:

250 folders (i folder per customer), each with 27 (months of info) notepad files, I need to write a macro which can open each notepad file for each customer then copy and paste the data into a different worksheet in a spreadsheet.

Does anyone have any ideas?

Cheers guys

Nick

 

[Richard Schollar]

How many lines of data is in each of the text files contained within the region folders?

 

[DaddyH]

Hi there, there is always at least 3 lines in each file. I just double checked the first customer and that has 3 lines in each of the 27 files but has returned 81 lines of data

Some of the customers could have upto 30 lines of data per file.


Arhhhh! ok just realised what it is doing! Say customer 1, region one has 3 lines of data that is repeating the same file 3 times. if ther customer has 5 lines of dat it is repaeting the filename 5 times.

Cheers

Nick

 

[Richard Schollar]

Yes - first three columns will contain as many duplicates as there are rows of data in each file. Sorry, should have explained. If you pivot the data with those as the row fields, then you will get the data summarised.

Does it provide you with the data that you hoped for?

 

[DaddyH]

ok, i think i am getting there, but how do i pivot the links to the data. i know how to pivot, but how do i return the data to get it to pivot from the links?

Nick

 

[Richard Schollar]

You won't get the links to the source file once you have pivoted the data - they will only be present in the data table that the macro creates.

 

[DaddyH]

Hi, when pivoting all i get is the path name not the info in it! i'm obviuolsy doing something wrong? any ideas?

 

[Richard Schollar]

Are you dragging the relevant fields into the Data region of your pivot table?

 

[DaddyH]

Arrrrhh! ok now i see! now i have a pivot table with 27 files per customer per county!.

How can i get all the info out of each link into the same work tab per customer per county.

Nick

 

[Richard Schollar]

In the pivot table, you need to drag the Customer column into the row field, followed by the Region column also into the row field. Then you need to drag the Value column into the Data field. This should then give you a summary of all the values per region per customer. Should you want the date information too (eg to summarise by year) then you need to drag those fields into the Row Field region too.

The following site contains a lovely introduction to pivot tables and should explain everything you will need:

http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[vaskov17]

.