How to create a notepad with character position and character length

[apeter5]

In Excel,

AN201	BOS	306=1234	035=Yes	102=Yes	100=70	097=Yes	Sometext shouldcome after a longspace thankyou
AN201	BOS	306=1235	035=No	102=No	100=71	097=No	This is second scenario thankyou

<tbody>
</tbody>

And the OUTPUT should be in NOTEPAD,

AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#Sometext shouldcome after a longspace thankyou
AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#This is second scenario thankyou

And the script which I tried is,

Sub Characterpostion()


Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
Dim str As String
Dim cellvalue As String


LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row


FilePath = FreeFile
CellData = ""


Open "C:\Users\Antony\Music\Excel Macros\Text2.txt" For Output As #2


For i = 1 To LastRow
For j = 1 To LastCol


Select Case j
Case 1
n = 5 - Len(Trim(ActiveCell(i, j).Value))
Case 2
n = 3 - Len(Trim(ActiveCell(i, j).Value))
Case 3
n = 8 - Len(Trim(ActiveCell(i, j).Value))
Case 4
n = 7 - Len(Trim(ActiveCell(i, j).Value))
Case 5
n = 7 - Len(Trim(ActiveCell(i, j).Value))
Case 6
n = 6 - Len(Trim(ActiveCell(i, j).Value))
Case 7
n = 7 - Len(Trim(ActiveCell(i, j).Value))

End Select




CellData = CellData & Space & Trim(ActiveCell(i, j).Value) & "@#"


If CellData Like "*=*" Then
Dim WrdArray() As String
WrdArray() = Split(CellData, "=")
str = WrdArray(0) + WrdArray(1)
Print #2 , str

Else
End If


Next j


Print #2 , CellData


CellData = ""


Next i


Close #2


End Sub



But CellData stores all the case at a time... I am not able to null the CellData to restore the new value.

Appreciate your immediate response!! thanks

 

[Rick Rothstein]

In Excel,

AN201	BOS	306=1234	035=Yes	102=Yes	100=70	097=Yes	Sometext shouldcome after a longspace thankyou
AN201	BOS	306=1235	035=No	102=No	100=71	097=No	This is second scenario thankyou

<tbody>
</tbody>

And the OUTPUT should be in NOTEPAD,

AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#Sometext shouldcome after a longspace thankyou
AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#This is second scenario thankyou

First off, I assume the second output is not exactly what you want (some of the fields appear to be copies from the first output when they look like they should have been from the second one). Given that, the following code should create a text file in the location you specified with the name you specified constructed from the data on the active sheet (first data item being in cell A1).

[table="width: 500"]
[tr]
	[td]Sub CreateTextFile()
  Dim R As Long, FF As Long, Data As Variant, Records() As String
  Data = Range("A1").CurrentRegion
  ReDim Records(1 To UBound(Data))
  For R = 1 To UBound(Data)
    Records(R) = Replace(Join(Application.Index(Data, R, Application.Transpose(Evaluate("ROW(1:" & UBound(Data, 2) & ")"))), "@#"), "=", "")
  Next
  FF = FreeFile
  Open "c:\temp\TestDataDump.txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , Join(Records, vbNewLine)
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
End Sub[/td]
[/tr]
[/table]

 

[apeter5]

@Rick thanks for your immediate reply as the same the above code is working fine. thankyou!!

 

[apeter5]

And also Rick I am trying to create a template for Notepad, is that possible?? For instance,

	Field	Line # <tbody> </tbody>	Column <tbody> </tbody>	Length <tbody> </tbody>	type <tbody> </tbody>
FIRST MESSAGE	Name	Line 1	17 <tbody> </tbody>	10 <tbody> </tbody>	string <tbody> </tbody>
	Father's Name <tbody> </tbody>	Line 2 <tbody> </tbody>	5 <tbody> </tbody>	12 <tbody> </tbody>	string <tbody> </tbody>
	DOB <tbody> </tbody>	Line 2 <tbody> </tbody>	11 <tbody> </tbody>	8 <tbody> </tbody>	Date <tbody> </tbody>
	CODE <tbody> </tbody>	Line 3 <tbody> </tbody>	12 <tbody> </tbody>	5 <tbody> </tbody>	Number <tbody> </tbody>
	END <tbody> </tbody>	Line 4 <tbody> </tbody>	1 <tbody> </tbody>	4 <tbody> </tbody>	string <tbody> </tbody>
SECOND MESSAGE <tbody> </tbody>	Name <tbody> </tbody>	Line 1 <tbody> </tbody>	17 <tbody> </tbody>	10 <tbody> </tbody>	string <tbody> </tbody>
	Father's Name <tbody> </tbody>	Line 2 <tbody> </tbody>	5 <tbody> </tbody>	12 <tbody> </tbody>	string <tbody> </tbody>
	DOB <tbody> </tbody>	Line 2 <tbody> </tbody>	11 <tbody> </tbody>	8 <tbody> </tbody>	Date <tbody> </tbody>
	CODE <tbody> </tbody>	Line 3 <tbody> </tbody>	12 <tbody> </tbody>	5 <tbody> </tbody>	Number <tbody> </tbody>
	END <tbody> </tbody>	Line 4 <tbody> </tbody>	1 <tbody> </tbody>	4 <tbody> </tbody>	string <tbody> </tbody>

<tbody>
</tbody>

And the Sample Output Notepad File should be ,

Need help to create a template from Excel to Notepad with exact character positions.

 

[apeter5]

Can anyone help me on this pls