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

 

[DaddyH]

Morning Richard, i am struggling here! please help me once again.
I'm fine with creating pivot tables and regard myself as above average in using them. however i don't think i have the data in the correct form.

As it stands at the minute in the data set i have 7 column headers:

Customer
Region
Link to source
First Column
Year
Month
Value

The Customer column is populated with the customer names. The Regions are populated with the correct region that links to the customer. Column 3 (Link to source) has a hyperlink to the data. Columns "First Colmun" to "Value" are blank.

In the pivot table I have Customer and Region in the Row Field if i put the "link to source" column in the Values column i just get a count of the lines. Even by changing this to product it doesn't give me any information.

What am i doing wrong?

Cheers Again

Nick

 

[Richard Schollar]

Hi Nick

When I tested the code I created, I used your sample data to save down several text files (containing the data) and ran the code - this is the output I got:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style="font-weight: bold;;">Customer Name</td><td width="63px" style="font-weight: bold;;">Region Name</td><td width="63px" style="font-weight: bold;;">Link to source file</td><td width="63px" style="font-weight: bold;;">First Column Value</td><td width="63px" style="font-weight: bold;;">Year</td><td width="63px" style="font-weight: bold;;">Month</td><td width="63px" style="font-weight: bold;;">Value</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">30</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">0</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">0</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">3</td><td width="63px" style="text-align: right;;">146037</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">4</td><td width="63px" style="text-align: right;;">43808</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">5</td><td width="63px" style="text-align: right;;">26323</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">6</td><td width="63px" style="text-align: right;;">23672</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">8</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">7</td><td width="63px" style="text-align: right;;">19246</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">9</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">8</td><td width="63px" style="text-align: right;;">20915</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">10</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">9</td><td width="63px" style="text-align: right;;">21906</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">11</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">10</td><td width="63px" style="text-align: right;;">42298</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">12</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">11</td><td width="63px" style="text-align: right;;">25716</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">13</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2009</td><td width="63px" style="text-align: right;;">12</td><td width="63px" style="text-align: right;;">46824</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">14</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">1</td><td width="63px" style="text-align: right;;">30241</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">15</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">2</td><td width="63px" style="text-align: right;;">27209</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">16</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">3</td><td width="63px" style="text-align: right;;">25620</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">17</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">4</td><td width="63px" style="text-align: right;;">29398</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">18</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">5</td><td width="63px" style="text-align: right;;">32697</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">19</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">6</td><td width="63px" style="text-align: right;;">32195</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">20</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">7</td><td width="63px" style="text-align: right;;">68743</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">21</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">8</td><td width="63px" style="text-align: right;;">174692</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">22</td><td width="63px" style=";">Customer 1</td><td width="63px" style=";">Region 1</td><td width="120px" style="text-decoration: underline;color: #0000FF;;">C:\Users\Richard\Documents\SpreadSheets\MrExcel\Parent\Customer 1\Region 1\Txt1 (1).txt</td><td width="63px" style="text-align: right;;">0</td><td width="63px" style="text-align: right;;">2010</td><td width="63px" style="text-align: right;;">9</td><td width="63px" style="text-align: right;;">35377</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet6</p><br /><br />

If the final 4 columns in your data table are completely blank then it suggests that the source files are blank (which is odd) - are you sure that columns D onwards are blank? Is there something in column D?

If there isn't, is there any chance you could physically provide me with one of these customer files (that has data)? You can remove any identifying names etc, I just need the data within it (which hasn't been modified). I will PM you with my email address.