Hi all, I need some help in consolidating all the workbooks into one.
Everyday I recieve one file, showing all the trades.
I wish to consolidate all the workbooks into one Summary Workbook.
So that I can continue to do some reporting.
The daily files stores in a folder C:\trades\Mar\
The Summary WorkBook is in C:\trades\Report\
As the daily files classify the trades into different tab for different products, I wish to copy the detail over to the summary workbook under the correct worksheets(tab)
File1(Spot Tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 71px;"><col style="width: 104px;"><col style="width: 55px;"><col style="width: 31px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 63px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 16px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="font-family: Courier; text-align: left;">Trade ID</td><td style="font-family: Courier; text-align: left;">Trade Number</td><td style="font-family: Courier; text-align: left;">Folder</td><td style="font-family: Courier; text-align: left;">B/S</td><td style="font-family: Courier; text-align: left;">Cpty</td><td style="font-family: Courier; text-align: left;">Cpty Name</td><td style="font-family: Courier; text-align: left;">Trade Date</td><td style="font-family: Courier; text-align: left;">Value</td><td style="font-family: Courier; text-align: left;">Pair</td><td style="font-family: Courier; text-align: left;">Spot Rate</td><td style="font-family: Courier; text-align: left;">Amount1</td><td style="font-family: Courier;">Mode</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">123</td><td style="text-align: right;">111</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">13/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.23</td><td style="text-align: right;">100000</td><td>Generated</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">321</td><td style="text-align: right;">222</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">13/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1</td><td style="text-align: right;">100000</td><td>Captured</td></tr></tbody></table>
File 2(Spot Tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 71px;"><col style="width: 104px;"><col style="width: 55px;"><col style="width: 31px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 63px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 16px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="font-family: Courier; text-align: left;">Trade ID</td><td style="font-family: Courier; text-align: left;">Trade Number</td><td style="font-family: Courier; text-align: left;">Folder</td><td style="font-family: Courier; text-align: left;">B/S</td><td style="font-family: Courier; text-align: left;">Cpty</td><td style="font-family: Courier; text-align: left;">Cpty Name</td><td style="font-family: Courier; text-align: left;">Trade Date</td><td style="font-family: Courier; text-align: left;">Value</td><td style="font-family: Courier; text-align: left;">Pair</td><td style="font-family: Courier; text-align: left;">Spot Rate</td><td style="font-family: Courier; text-align: left;">Amount1</td><td style="font-family: Courier;">Mode</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right;">444</td><td style="text-align: right;">333</td><td>DP</td><td>B</td><td>DDD</td><td>DDD</td><td style="text-align: right;">16/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.3</td><td style="text-align: right;">20000</td><td>Captured</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right;">555</td><td style="text-align: right;">444</td><td>ND</td><td>S</td><td>SSS</td><td>SSS</td><td style="text-align: right;">17/3/2009</td><td style="text-align: right;">16/3/2009</td><td>EUR</td><td style="text-align: right;">1.5</td><td style="text-align: right;">300000</td><td>Captured</td></tr></tbody></table>
Summary(Spot tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="font-family: Courier; text-align: left;">Trade ID</td><td style="font-family: Courier; text-align: left;">Trade Number</td><td style="font-family: Courier; text-align: left;">Folder</td><td style="font-family: Courier; text-align: left;">B/S</td><td style="font-family: Courier; text-align: left;">Cpty</td><td style="font-family: Courier; text-align: left;">Cpty Name</td><td style="font-family: Courier; text-align: left;">Trade Date</td><td style="font-family: Courier; text-align: left;">Value</td><td style="font-family: Courier; text-align: left;">Pair</td><td style="font-family: Courier; text-align: left;">Spot Rate</td><td style="font-family: Courier; text-align: left;">Amount1</td><td style="font-family: Courier;">Mode</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right;">987</td><td style="text-align: right;">111</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">14/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.23</td><td style="text-align: right;">100000</td><td>Generated</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">237</td><td style="text-align: right;">222</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">15/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1</td><td style="text-align: right;">100000</td><td>Captured</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">444</td><td style="text-align: right;">333</td><td>DP</td><td>B</td><td>DDD</td><td>DDD</td><td style="text-align: right;">16/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.3</td><td style="text-align: right;">20000</td><td>Captured</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">555</td><td style="text-align: right;">444</td><td>ND</td><td>S</td><td>SSS</td><td>SSS</td><td style="text-align: right;">17/3/2009</td><td style="text-align: right;">16/3/2009</td><td>EUR</td><td style="text-align: right;">1.5</td><td style="text-align: right;">300000</td><td>Captured</td></tr></tbody></table>
Summary(Menu tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Directory</td><td>C:\trades\Mar\</td><td> </td></tr></tbody></table>
Everyday I recieve one file, showing all the trades.
I wish to consolidate all the workbooks into one Summary Workbook.
So that I can continue to do some reporting.
The daily files stores in a folder C:\trades\Mar\
The Summary WorkBook is in C:\trades\Report\
As the daily files classify the trades into different tab for different products, I wish to copy the detail over to the summary workbook under the correct worksheets(tab)
File1(Spot Tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 71px;"><col style="width: 104px;"><col style="width: 55px;"><col style="width: 31px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 63px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 16px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="font-family: Courier; text-align: left;">Trade ID</td><td style="font-family: Courier; text-align: left;">Trade Number</td><td style="font-family: Courier; text-align: left;">Folder</td><td style="font-family: Courier; text-align: left;">B/S</td><td style="font-family: Courier; text-align: left;">Cpty</td><td style="font-family: Courier; text-align: left;">Cpty Name</td><td style="font-family: Courier; text-align: left;">Trade Date</td><td style="font-family: Courier; text-align: left;">Value</td><td style="font-family: Courier; text-align: left;">Pair</td><td style="font-family: Courier; text-align: left;">Spot Rate</td><td style="font-family: Courier; text-align: left;">Amount1</td><td style="font-family: Courier;">Mode</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">123</td><td style="text-align: right;">111</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">13/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.23</td><td style="text-align: right;">100000</td><td>Generated</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">321</td><td style="text-align: right;">222</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">13/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1</td><td style="text-align: right;">100000</td><td>Captured</td></tr></tbody></table>
File 2(Spot Tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 71px;"><col style="width: 104px;"><col style="width: 55px;"><col style="width: 31px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 39px;"><col style="width: 79px;"><col style="width: 63px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 16px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="font-family: Courier; text-align: left;">Trade ID</td><td style="font-family: Courier; text-align: left;">Trade Number</td><td style="font-family: Courier; text-align: left;">Folder</td><td style="font-family: Courier; text-align: left;">B/S</td><td style="font-family: Courier; text-align: left;">Cpty</td><td style="font-family: Courier; text-align: left;">Cpty Name</td><td style="font-family: Courier; text-align: left;">Trade Date</td><td style="font-family: Courier; text-align: left;">Value</td><td style="font-family: Courier; text-align: left;">Pair</td><td style="font-family: Courier; text-align: left;">Spot Rate</td><td style="font-family: Courier; text-align: left;">Amount1</td><td style="font-family: Courier;">Mode</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right;">444</td><td style="text-align: right;">333</td><td>DP</td><td>B</td><td>DDD</td><td>DDD</td><td style="text-align: right;">16/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.3</td><td style="text-align: right;">20000</td><td>Captured</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right;">555</td><td style="text-align: right;">444</td><td>ND</td><td>S</td><td>SSS</td><td>SSS</td><td style="text-align: right;">17/3/2009</td><td style="text-align: right;">16/3/2009</td><td>EUR</td><td style="text-align: right;">1.5</td><td style="text-align: right;">300000</td><td>Captured</td></tr></tbody></table>
Summary(Spot tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="font-family: Courier; text-align: left;">Trade ID</td><td style="font-family: Courier; text-align: left;">Trade Number</td><td style="font-family: Courier; text-align: left;">Folder</td><td style="font-family: Courier; text-align: left;">B/S</td><td style="font-family: Courier; text-align: left;">Cpty</td><td style="font-family: Courier; text-align: left;">Cpty Name</td><td style="font-family: Courier; text-align: left;">Trade Date</td><td style="font-family: Courier; text-align: left;">Value</td><td style="font-family: Courier; text-align: left;">Pair</td><td style="font-family: Courier; text-align: left;">Spot Rate</td><td style="font-family: Courier; text-align: left;">Amount1</td><td style="font-family: Courier;">Mode</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right;">987</td><td style="text-align: right;">111</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">14/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.23</td><td style="text-align: right;">100000</td><td>Generated</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">237</td><td style="text-align: right;">222</td><td>ND</td><td>B</td><td>TDG</td><td>TDG</td><td style="text-align: right;">15/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1</td><td style="text-align: right;">100000</td><td>Captured</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">444</td><td style="text-align: right;">333</td><td>DP</td><td>B</td><td>DDD</td><td>DDD</td><td style="text-align: right;">16/3/2009</td><td style="text-align: right;">16/3/2009</td><td>USD</td><td style="text-align: right;">1.3</td><td style="text-align: right;">20000</td><td>Captured</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">555</td><td style="text-align: right;">444</td><td>ND</td><td>S</td><td>SSS</td><td>SSS</td><td style="text-align: right;">17/3/2009</td><td style="text-align: right;">16/3/2009</td><td>EUR</td><td style="text-align: right;">1.5</td><td style="text-align: right;">300000</td><td>Captured</td></tr></tbody></table>
Summary(Menu tab)
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Directory</td><td>C:\trades\Mar\</td><td> </td></tr></tbody></table>