VBA to convert to Txt file after setting the data to requirement

[Emmily]

Hi, we have an accounting software which only accepts data in txt. I need VBA to produce data as shown in the second screen shot, the first screen shot is what the data currently looks like, i need the code to produce the second screen shot and then create a copy of worksheet and save as txt. I will then upload this file.

The date in B2 or C2 is only there for you to see in my real data the date has named range rngDate.

Excel Workbook
	B	C
2	Date	20/03/2011
3
4
5	AJAJE	48.11
6	ALLOVER	18.56
7	AVILA	27.85
Sheet1

Excel Workbook
	A	B	C	D	E	F	G
2	Date	20/03/2011
3
4
5	20/03/2011	AJAJE/AAT	BANK	0	0	48.11	"Imported Interest Charge, Client Code=AJAJE"
6	20/03/2011	ALLOVER/AAT	BANK	0	0	18.56	"Imported Interest Charge, Client Code=ALLOVER"
7	20/03/2011	AVILA/AAT	BANK	0	0	27.85	"Imported Interest Charge, Client Code=AVILA"
8
Sheet1

 

[Emmily]

*bump*

 

[Emmily]

How would i get VBA to produce Col G especially getting the client code?

<table border="1" cellpadding="0" cellspacing="0"><tbody><tr><td style="text-align: right;">20/03/2011</td><td>AJAJE/AAT</td><td>BANK</td><td style="text-align: right;">0</td><td style="text-align: right;">0</td><td style="text-align: right;">48.11</td><td>"Imported Interest Charge, Client Code=AJAJE"</td></tr></tbody></table>