How to create a notepad with character position and character length

A

apeter5

New Member
Joined
Mar 20, 2018
Messages
7
In Excel,

[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1234[/TD]
[TD]035=Yes[/TD]
[TD]102=Yes[/TD]
[TD]100=70[/TD]
[TD]097=Yes[/TD]
[TD]Sometext shouldcome after a longspace thankyou[/TD]
[/TR]
[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1235[/TD]
[TD]035=No[/TD]
[TD]102=No[/TD]
[TD]100=71[/TD]
[TD]097=No[/TD]
[TD]This is second scenario thankyou[/TD]
[/TR]
</tbody>[/TABLE]


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(n) & 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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
apeter5 said:
In Excel,

[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1234[/TD]
[TD]035=Yes[/TD]
[TD]102=Yes[/TD]
[TD]100=70[/TD]
[TD]097=Yes[/TD]
[TD]Sometext shouldcome after a longspace thankyou[/TD]
[/TR]
[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1235[/TD]
[TD]035=No[/TD]
[TD]102=No[/TD]
[TD]100=71[/TD]
[TD]097=No[/TD]
[TD]This is second scenario thankyou[/TD]
[/TR]
</tbody>[/TABLE]

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
Click to expand...
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).
Code: 
[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]
 
Upvote 0
And also Rick I am trying to create a template for Notepad, is that possible?? For instance,

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]Field[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line #[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Column[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Length[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]FIRST MESSAGE[/TD]
[TD]Name[/TD]
[TD]Line 1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]string[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]Father's Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]string[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]DOB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]CODE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]END[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]string[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]SECOND MESSAGE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]string[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]Father's Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]string[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]DOB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]CODE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, width: 91"]END[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]string[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

And the Sample Output Notepad File should be ,

1l6at.jpg


Need help to create a template from Excel to Notepad with exact character positions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question Question
In VBA, How to create a template from Excel to Notepad with exact character positions
Replies
1
Views
1K
Joe4
Joe4
A
  • Question Question
vba code to create a csv file - how to choose rows with data in column A
Replies
3
Views
501
anteween
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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