how to convertion of excel to wordpad

[sukhwinder315]

i want to convert my excel file containing cheque no 11111 and amount 5000 in line 1, 123 amt 2500 in line 2in the following shape
00111110000500000
00001230000250000

 

[Joe4]

Wordpad is not a file format, it is a text file reader (it can read most any text file format).

It looks like you are trying to create a Fixed Width Text File. Here is how you can do what you want.

Insert a new sheet in your workbook (let's call it Sheet2). Then, assuming that your data starts in cell A1 on Sheet1, enter this formula in cell A1 on Sheet2.
=TEXT(Sheet1!A1,"000000")
Enter this formula in B1 on Sheet2:
=TEXT(Sheet1!B1*100,"0000000000")

Now, change the column width of column A on Sheet2 to 6,
and change the column width of column B on Sheet2 to 10.

Now, while on Sheet2, do a file "SaveAs" and save the file with the "Save as type" of "Formatted Text (Space delimited) (*.prn)".

Now, if you open this new file in WordPad, it should look like you want.

 

[sukhwinder315]

Thanx a lot it works exactly as i want. Sorry for sending dupliacte thread . actually i m new on this forum. My next question is that can u help me in forming a prog which will automatically form *.prn file as describrd by u and then convert it into .txt file . Actually i have seen a prog named trickle feed software in which on inputing data in excel sheet it converts it into .txt file automatically and place this file on c drive

 

[Joe4]

If you use the Macro Recorder, you can record most of the code you need to do this. Just turn on the Macro Recorder, and record yourself performing most the steps manually. After you have that, we can help you edit the code to make it more dynamic (and add the things which you cannot do with the Macro Recorder).

So why don't you start there, see how it works, then post the code and let us know which changes need to be made?

 

[sukhwinder315]

sir, i have no knowledge of using macros and have only initial know how of excel. so can u explain me in detail. i want to input three values in sheet 1

 

[Joe4]

You don't really need to know anything at all about macros. You just record the steps you perform manually, and it will create the macro for you. You don't really need macros at all, they are just useful if you have to perform the same process in the future, and don't want to do it manually every time.

Basically, in my last post to you, I stepped you though the process of how to do what you want. Follow those steps, and it should get you what you need.

If you want to make it a macro, read here on how to record a macro: http://www.officearticles.com/excel/record_a_macro_in_microsoft_excel.htm
(it may be found in a different place, depending on which version of Excel you are using).

 

[sukhwinder315]

Sir, i have tried the macro recording nad tried to place the text file on desktop
but when i run the macro it gives message " the macro boo1.prn cannot be found". following is the vba.
can u help now how to create automatic file at desktop

Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "=TEXT(Sheet1!RC,""000000"")"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=TEXT(Sheet1!RC*100,""00000000000000"")"
Columns("A:A").Select
Selection.ColumnWidth = 6
Columns("B:B").Select
Selection.ColumnWidth = 14
Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A1:B135"), Type:=xlFillDefault
Range("A1:B135").Select
ActiveWindow.SmallScroll Down:=-27
Range("C84").Select
ActiveWindow.SmallScroll Down:=-93
ChDir "C:\Documents and Settings\Administrator\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.prn", FileFormat:= _
xlTextPrinter, CreateBackup:=False
End Sub

 

[Joe4]

Try this cleaned-up version, with some dynamic coding added in:

Sub MyExport()

    Dim myLastRow As Long
    Dim myDir As String
    
'   Find last row in column A on sheet 1
    myLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

'   Enter formulas on sheet 2
    Sheets("Sheet2").Range("A1:A" & myLastRow).FormulaR1C1 = "=TEXT(Sheet1!RC,""000000"")"
    Sheets("Sheet2").Range("B1:B" & myLastRow).FormulaR1C1 = "=TEXT(Sheet1!RC*100,""00000000000000"")"
    
'   Set column widths
    Sheets("Sheet2").Columns("A:A").ColumnWidth = 6
    Sheets("Sheet2").Columns("B:B").ColumnWidth = 14

'   Check for existence of directory and export file
    Sheets("Sheet2").Select
    myDir = "C:\Documents and Settings\Administrator\Desktop\"
    
    If Dir(myDir, vbDirectory) = "" Then
        MsgBox "Directory " & myDir & " does not exist!!!"
    Else
        ActiveWorkbook.SaveAs Filename:=myDir & "Book1.prn", FileFormat:= _
            xlTextPrinter, CreateBackup:=False
    End If

End Sub