Macro to read excel data and write to a Text file

[princesakthi]

My Excel Data has two coloms
1st Column is Bank A/c No & 2nd Column is Amount

Excel Data

Bank No Amount
17755 65000
17692 25000
17456 1500
25005 500
27201 10
26845 2

Required text File Data

0250010000.17755.00065000.00
0250010000.17692.00025000.00
0250010000.17456.00001500.00
0250010000.27201.00000010.00
0250010000.26845.00000002.00

Before Bank A/c No Add "0250010000."
Before Amount Add ".000" or ".0000" or ".00000"or ".000000"or ".000000"

if the Amount Greater than or equal to 10000, ".000"
if the Amount Greater than or equal to 1000, ".0000"
if the Amount Greater than or equal to 100, ".00000"
if the Amount Greater than or equal to 10, ".000000"
if the Amount Greater than or equal to 1, ".0000000"

After Amount Add ".00"

Thanks is Advance

 

[wigi]

Hello

Here is the formula to piece together the long string:

="0250010000." & A1 & "." & REPT(0,7-INT(LN(B1)/LN(10))) & B1 & ".00"

Now you can "save as" the file as a text document, or use VBA-code that you can find on the internet and in other topics, to export a column to a text file.

 

[ParamRay]

.
.

Try running the following ExportToText sub procedure.
Make sure that your worksheet is active before running it.

Private Function convert_data(val1, val2)

    convert_data = "0250010000." & val1 & "." & Format(val2, "00000000") & ".00"

End Function

'-------------------------------------------------------------------------------

Sub ExportToText()

    Dim last_row As Long
    Dim i As Long
    Dim arr() As String
    Dim FileHandle As Integer
    Dim FileName As String
    
    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim arr(1 To last_row - 1)
    
    For i = 2 To last_row
        arr(i - 1) = convert_data( _
            Cells(i, 1).Value, _
            Cells(i, 2).Value)
    Next i
    
    FileHandle = FreeFile
    FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
        Application.PathSeparator & "bank_accounts.txt"
    
    Open FileName For Output As FileHandle
    For i = LBound(arr) To UBound(arr)
        Print #FileHandle, arr(i)
    Next i
    Close #FileHandle
    
    MsgBox _
        Prompt:="New file created:" & vbCrLf & FileName, _
        Buttons:=vbInformation

End Sub

 

[wigi]

Thanks gpeacock, I thought my formula was too complicated...

="0250010000." & A1 & "." & TEXT(B1,"0000000") & ".00"

 

[princesakthi]

Thanks gpeacock

It is working Properly.

 

[princesakthi]

Thanks wigi
for Your Kind Reply.