Macro to convert data into a text file in special format

[swilson2006]

Hi all

I am hoping someone can help with a macro to do the following.....

I have my data in in this format:

Date	FX Rate	Conv Code
30/10/2013	1.1681	EUR
29/10/2013	1.1717	EUR
28/10/2013	1.1711	EUR

I need to take each row in the file and convert into this format.

DC
C
EUR
30102013
<>
<>
/
1.1681
<>
<>
<>
<>
DC
C
EUR
29102013
<>
<>
/
1.1717
<>
<>
<>
<>
DC
C
EUR
28102013
<>
<>
/
1.1711
<>
<>
<>
<>

[ragulduy]

Something like:

Please Login or Register  to view this content.

[nilem]

or

Please Login or Register  to view this content.

[swilson2006]

I get run-time error 1004 on this
Method "Range" of object_Global failed.

[swilson2006]

Something like:

Please Login or Register  to view this content.

I get run-time error 1004 on this
Method "Range" of object_Global failed.

[swilson2006]

or

Please Login or Register  to view this content.

This macro did work until it got to the dates section. 09/10/2013 was entered as 9102013 and 08/10/2013 was entered as 8102013.

Any way to correct this?

Great solution though - thanks so much!

[nilem]

Please Login or Register  to view this content.

[ragulduy]

For what it's worth, mine should have been:

Please Login or Register  to view this content.

[swilson2006]

For what it's worth, mine should have been:

Please Login or Register  to view this content.

Thanks for that- this worked perfectly.

Is there a way to adapt this macro so that it can also do the following:

In cell F1 I have the following values "ASL,CPL,ALL,APL,ACO,CZZ"
I want to be able to loop through each three letter code in cell F1 to create data for each entity in the list.

Sub macro_1()
Dim fs, fil, count
Set fs = CreateObject("Scripting.filesystemobject")
Set fil = fs.createtextfile("E:\test.txt")
For count = 2 To Range("A" & Rows.count).End(xlUp).row
fil.writeline "SS"
fil.writeline "XXX" - THIS IS THE THREE LETTERS IN CELL F1
fil.writeline "LA"
fil.writeline "DC"
fil.writeline "C"
fil.writeline Range("C" & count)
fil.writeline Replace(Range("A" & count).text, "/", "")
fil.writeline "<>"
fil.writeline "<>"
fil.writeline "/"
fil.writeline Range("B" & count)
fil.writeline "<>"
fil.writeline "<>"
fil.writeline "<>"
fil.writeline "<>"
Next
End Sub

This would then achieve exactly what I needed. As currently I have to import that file for each entity where as this script would create the necessary script for each entity.

Many thanks

S

[ragulduy]

Please Login or Register  to view this content.

[swilson2006]

Please Login or Register  to view this content.

Hi Yudlugar

I get that error again - error 1004 on this row

fil.writeline Range("C" & count)

[ragulduy]

sorry, it's because the first iteration of the loop is 0. I'm not sure I full understand though, did you want to loop through every row, for every instance of XXX? or do you have the same amount of XXX's as you do rows, in the case of the former:

Please Login or Register  to view this content.

for the latter:

Please Login or Register  to view this content.

[swilson2006]

sorry, it's because the first iteration of the loop is 0. I'm not sure I full understand though, did you want to loop through every row, for every instance of XXX? or do you have the same amount of XXX's as you do rows, in the case of the former:

Please Login or Register  to view this content.

for the latter:

Please Login or Register  to view this content.

Thanks for your help.

For every XXX in field F1 I should have exactly the same rows.

Neither of these seem to work though.

[ragulduy]

Could you upload a sample workbook please, click go advanced then manage attachments.

[swilson2006]

Could you upload a sample workbook please, click go advanced then manage attachments.

SUN FX Rates.xlsm

See the attached excel file

[ragulduy]

Please Login or Register  to view this content.

I tested that and it seemed ok. Made a few errors in the variable definitions last time.

[swilson2006]

Please Login or Register  to view this content.

I tested that and it seemed ok. Made a few errors in the variable definitions last time.

It's not quite working as yet. It seems to be including the column C heading of "CONVCODE" instead of looping through column C from row 2 onwards.

[ragulduy]

One more go:

Please Login or Register  to view this content.

[swilson2006]

One more go:

Please Login or Register  to view this content.

Works brilliantly! Thanks very much for your help.

Have a great evening.