Macro to read excel data and write to a Text file

princesakthi

New Member
Joined
Nov 10, 2012
Messages
3
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
.
.

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

Code:
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
 
Upvote 0
Thanks gpeacock, I thought my formula was too complicated...

Code:
="0250010000." & A1 & "." & TEXT(B1,"0000000") & ".00"
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,695
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top