Appendix Thread. ( Codes for other Threads, ( Avinash ).)

In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

Row\Col	E	F	G	H	I	J	K	L	M	N	O	P	Q
6				#VALUE!		Got missing number in column E		CSE equivalent		CSE equivalent
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge	1000.00				1000		Got one or more missing numbers
9		1	eileenslounge1	4.00				4		Got missing number in column E
10	1	2	eileenslounge2	9.00				9
11	2	3	Others	16.00				16
12	3	4	eileenslounge	1000.00				1000
13	4	5	eileenslounge1	36.00				36
14	5	6	eileenslounge2	49.00				49
15	6	7	Others	64.00				64
16	7	8
17	8

Worksheet: data

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

Row\Col	E	F	G	H	I	J	K	L	M	N	O	P	Q
6				=IF(G6="eileenslounge",1000,F7*E8)		=IF(F7="","Got one or more missing numbers",IF(E8="","Got missing number in column E",""))		CSE equivalent		CSE equivalent
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge	=IF(G8="eileenslounge",1000,F9*E10)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
9		1	eileenslounge1	=IF(G9="eileenslounge",1000,F10*E11)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
10	1	2	eileenslounge2	=IF(G10="eileenslounge",1000,F11*E12)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
11	2	3	Others	=IF(G11="eileenslounge",1000,F12*E13)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
12	3	4	eileenslounge	=IF(G12="eileenslounge",1000,F13*E14)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
13	4	5	eileenslounge1	=IF(G13="eileenslounge",1000,F14*E15)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
14	5	6	eileenslounge2	=IF(G14="eileenslounge",1000,F15*E16)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
15	6	7	Others	=IF(G15="eileenslounge",1000,F16*E17)				=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
16	7	8
17	8

Worksheet: data

In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

Row\Col	E	F	G	H	I	J	K	L	M	N	O
5								CSE equivalent		CSE equivalent
6				=IF(G6="eileenslounge",1000,F7*E8)		=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
9		1	eileenslounge1					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
10	1	2	eileenslounge2					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
11	2	3	Others					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
12	3	4	eileenslounge					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
13	4	5	eileenslounge1					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
14	5	6	eileenslounge2					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
15	6	7	Others					=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E17)		=IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
16	7	8
17	8
18

Worksheet: data


_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

Row\Col	E	F	G	H	I	J	K	L	M	N	O
5								CSE equivalent		CSE equivalent
6				#VALUE!		Got missing number in column F
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge					1000		Got one or more missing numbers
9		1	eileenslounge1					4		Got one or more missing numbers
10	1	2	eileenslounge2					9
11	2	3	Others					16
12	3	4	eileenslounge					1000
13	4	5	eileenslounge1					36
14	5	6	eileenslounge2					49
15	6	7	Others					64
16	7	8
17	8
18

Worksheet: data

Macro accomnpanying last post

Code:

---

Sub EvaluateRangeFormulasC() '    https://eileenslounge.com/viewtopic.php?p=268537#p268537
Dim Ws As Worksheet, Rng As Range, Clm As Range, lRow As Long
Const fRow As Long = 6: Const sRow As Long = 8
 Set Ws = ThisWorkbook.Worksheets("data")
' Let lRow = Ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Let lRow = Ws.Range("G" & Ws.Rows.Count & "").End(xlUp).Row    '  '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
  On Error Resume Next
 Set Rng = Ws.Rows(fRow).SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0
    If Rng Is Nothing Then MsgBox "No formulas!": Exit Sub

  Let Application.ScreenUpdating = False
    For Each Clm In Rng
    Dim strEval As String '                                                                    '  Formula in column H                                    Formula in column J
    Let strEval = Clm.Formula: Debug.Print strEval                                            '  =IF(G6="eileenslounge",1000,F7*E8)                    =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
    ' modifications to make first formula work in  CSE / Range Evaluate  sort of a way
    Let strEval = Replace(strEval, "G6", "G8:G" & lRow & ""): Debug.Print strEval              '  =IF(G8:G15="eileenslounge",1000,F7*E8)                =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
    Let strEval = Replace(strEval, "F7*E8", "F9:F16*E10:E17" & lRow & ""): Debug.Print strEval '  =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)      =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
    Debug.Print ' just to make an emty line in the Immediate window
    ' modifications required for second  formula work in  CSE / Range Evaluate  sort of a way
    Let strEval = Replace(strEval, "E7", "E8:E15" & lRow & ""): Debug.Print strEval            '  =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)      =IF(E8:E1515="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
    Let strEval = Replace(strEval, "F8", "F8:F15" & lRow & ""): Debug.Print strEval            '  =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)      =IF(E8:E1515="","Got one or more missing numbers",IF(F8:F1515="","Got missing number in column F",""))
    Let Clm.Offset(sRow - fRow).Resize(lRow - sRow + 1).Value = Evaluate(strEval)
    Debug.Print ' just to make an emty line in the Immediate window
    Next Clm
 
 Let Application.ScreenUpdating = True
End Sub

---

Running the above macro on the test data in uploade file will give these results:

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

Row\Col	E	F	G	H	I	J	K	L	M	N	O
5								CSE equivalent		CSE equivalent
6				#VALUE!		Got missing number in column F
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge	1000.00		Got one or more missing numbers		1000		Got one or more missing numbers
9		1	eileenslounge1	4.00		Got one or more missing numbers		4		Got one or more missing numbers
10	1	2	eileenslounge2	9.00				9
11	2	3	Others	16.00				16
12	3	4	eileenslounge	1000.00				1000
13	4	5	eileenslounge1	36.00				36
14	5	6	eileenslounge2	49.00				49
15	6	7	Others	64.00				64
16	7	8
17	8
18

Worksheet: data

---

When in the VB Editor, after running the macro, you can hit keys Ctrl+g to see the following in the Immediate window. It shows the build up of the formulas in a full run

Code:

---

=IF(G6="eileenslounge",1000,F7*E8)
=IF(G8:G15="eileenslounge",1000,F7*E8)
=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)

=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)
=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)

=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))

=IF(E8:E1515="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
=IF(E8:E1515="","Got one or more missing numbers",IF(F8:F1515="","Got missing number in column F",""))

---

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706


.csv file before
http://www.eileenslounge.com/viewtopic.php?f=30&t=34497
After downloading the
ALERT.xlsx
file at that post , I navigsted to it using Windows file explorer and physically changed it in the explorer window without opening it to
Alert29Apr..csv

Double clicking that gives this
_____ Workbook: Alert29Apr..csv ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1		22
2		25
3		15083
4		17388
5

Worksheet: ALERT

The string of thet file has 9096 Characters!! : https://pastebin.com/Ptk0f7S8
Share ‘9096Characters29Apr.xls’ : https://app.box.com/s/8g72lokzoil9fe6j645xcg8hej82gcn7

This is how it opens in Notepads
9096Characters29AprTextNotepads.JPG : https://imgur.com/USuCebF
Attachment 2928
One of the few things I can see of any sense is towards the start is a "[Content_Types].xml" : -
"Content" & "_" & "Types" & Chr(93) & "." & "xml"
[Content_Types].xml

_____ Workbook: 9096Characters29Apr.xls ( Using Excel 2007 32 bit )

30	2
31 [	91
32 C	67
33 o	111
34 n	110
35 t	116
36 e	101
37 n	110
38 t	116
39 _	95
40 T	84
41 y	121
42 p	112
43 e	101
44 s	115
45 ]	93
46 .	46
47 x	120
48 m	109
49 l	108
50	32

This sort of macro gets the long file string.

Code:

---

Sub WhatStringIsInAlertDotCSV() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=34497
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\At Eileen\" & "Alert29Apr..csv" ' This  would be made  if not existing and we would have a zero lenf string
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
Dim Lenf As Long: Let Lenf = LOF(FileNum)
TotalFile = Space(Lenf) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' What the fuck is in this string?
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)  '  ' https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11015&viewfull=1#post11015
End Sub

---

---

There are no issues with the file format changing or in not getting the required format if this file is opened saved closed etc.. manually or using the below macro.
Further we see that we can change things, and even add worksheets, save and reopen... All changes and any added worksheets are still there!!
We are beginig to see the problem, or rather another twist in the confusion that is Avinash

Code:

---

Sub OpenEileensAlertDotCSV() ' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13343&viewfull=1#post13343
Dim PathAndFileName As String
' The  following file was uploaded as  ALERT.xlsx  I dowloaded it and I navigsted to it using Windows file explorer and physically changed it in the explorer window without opening it to    Alert29Apr..csv
 Let PathAndFileName = ThisWorkbook.Path & "\At Eileen\" & "Alert29Apr..csv"
Dim Wb As Workbook, WbSaveSimp As Workbook, WbSaveComp As Workbook '  Ws1 As Worksheet
 Set Wb = Workbooks.Open(PathAndFileName)
 Set WbSaveSimp = Wb: Set WbSaveComp = Wb
Wb.Close: Set Wb = Workbooks.Open(PathAndFileName)
 Call WhatStringIsInAlertDotCSV
Wb.Save: Wb.Close
 Call WhatStringIsInAlertDotCSV
' ' No issues so far



End Sub

---

We are beginig to see the problem, or rather another twist in the confusion that is Avinash. We do not always have a .csv file!!!!! - I can see this for example if I manually try to open the file that typically "works" for Avinash Trying to open Alert when it is not a csv.JPG : https://imgur.com/sS2vnw0Attachment 2927
( Note: This warning does not appear when opening the file by a macro, such as in the macro above! )
If I try to do a simple Save on such a file either manually or with coding as in the above macro , then ir is done OK. If I attempt a SaveAs then it will want to save it as an Excel File: Wants to SaveAs xlsx file.JPG : https://imgur.com/RAH3E9T Attachment 2929

Furthermore , there is not an issue if I SaveAs manually with a Filename of "Alert29Apr..csv" ,
Save Alert with doubledot csv as xlsx Excel File.JPG
But , it will end up as a new file "Alert29Apr..csv.xlsx
There are not issues with SaveAs saving it with coding: These will give us our Excel file masquerading as a .csv file
Wb.SaveAs Filename:=ThisWorkbook.Path & "\At Eileen" & "Alert29Apr..csv"
Wb.SaveAs Filename:=ThisWorkbook.Path & "\At Eileen" & "Alert29AprRemove a dot.csv"
There are no issues in reopening these files in coding, and also manually if the warning, ( about the file not being the type of the extension ) is ignored

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706


Later

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706


Later

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706

---

VBA To Copy Rows From One Workbook To text csv File, Based On Count In A Different Workbook

Question
I have three files 2 Excel Files,1.xls & 3.xlsx , and a text file, 2.csv
1.xls first row has headers so don't count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that many rows of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything

The final result should be a comma separated values text file , 2.csv.
For example, in Notepad, it looks like this:
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs
Attachment 2935

That is the final result that I want

---

Answer:

Code:

---

Sub Step14() '    https://excelfox.com/forum/showthread.php/2467-VBA-To-Copy-Rows-From-One-Workbook-To-text-csv-File-Based-On-Count-In-A-Different-Workbook?p=13367&viewfull=1#post13367        '    http://www.eileenslounge.com/viewtopic.php?f=30&t=34508 (zyxw123)    https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13182#post13182
Rem 1 Worksheets info
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
 Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")  ' Workbooks("1.xls")                        ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xlsx")
 Set w2 = Workbooks.Open(ThisWorkbook.Path & "\2.csv")  ' Workbooks("2.csv")                        ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\document\2.csv")
 Set w3 = Workbooks.Open(ThisWorkbook.Path & "\3.xlsx")  ' Workbooks("3.xlsx")                        ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\files\3.xlsx")
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
 Set WS1 = w1.Worksheets.Item(1)
 Set WS2 = w2.Worksheets.Item(1)
 Set WS3 = w3.Worksheets.Item(1)
Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long
 Let Lr1 = WS1.Range("A" & WS1.Rows.Count & "").End(xlUp).Row      '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
 Let Lc3 = WS3.Cells.Item(1, WS3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
 Let Lc3Ltr = CL(Lc3)
Rem 2  ' In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv
 Let Lenf1 = Lr1 - 1                                                ' 1.xls first row has headers so dont count that
' 2a)
Dim rngOut As Range: Set rngOut = WS2.Range("A1:" & Lc3Ltr & Lenf1 & "")
'' 2b)(i)  Relative formula referrences  ... https://teylyn.com/2017/03/21/dollarsigns/#comment-191
' WS2.Cells.NumberFormat = "General"                          ' May be needed to prevent formulas coming out as test    =[3.xlsx]Sheet1!$A$1
' Let rngOut.Value = "='[3.xlsx]" & WS3.Name & "'!A$1"
' Let rngOut.Value = rngOut.Value  '  Change Formulas to values
' Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")      '                              https://excelribbon.tips.net/T010741_Removing_Spaces
' Or
' 2b)(ii) Copy Paste
Dim rngIn As Range
 Set rngIn = WS3.Range("A1:" & Lc3Ltr & "1")
 rngIn.Copy
 rngOut.PasteSpecial Paste:=xlPasteValues  '  understanding  Paste  across ranges of different size to  Copy  range : https://excelfox.com/forum/showthread.php/2221-VBA-Range-Insert-Method-Code-line-makes-a-space-to-put-new-range-in?p=10441&viewfull=1#post10441

Rem 3
' 3a
 w1.Close
 w3.Close
' 3b
w2.SaveAs Filename:=ThisWorkbook.Path & "\2.csv", FileFormat:=xlCSV
 Let Application.DisplayAlerts = False
 w2.Close
 Let Application.DisplayAlerts = True

End Sub


---

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706


Later


My first answer here was almost perfect. https://excelfox.com/forum/showthrea...ll=1#post13185
https://excelfox.com/forum/showthrea...ll=1#post13184

This was your question:
i have three files 1.xls & 2.csv & 3.xlsx
1.xls first row has headers so dont count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything


You question should have been you question:
VBA To Copy Rows From One Workbook To text csv File, Based On Count In A Different Workbook
I have three files 2 Excel Files,1.xls & 3.xlsx , and a text file, 2.csv
1.xls first row has headers so don’t count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that many rows of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything

The final result should be a comma separated values text file , 2.csv.
For example, in Notepad, it looks like this:
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs


That is the final result that I want


Here is the new solution from me : https://excelfox.com/forum/showthrea...ll=1#post13346

Only a very small change was required:

Code:

---

' 3b
 w2.SaveAs Filename:=ThisWorkbook.Path & "\2.csv", FileFormat:=xlCSV
 Let Application.DisplayAlerts = True
 w2.Close

---

---

Avinash
Read this, and try to understand at least a little of it.
2.csv is a test file. It is not an Excel file.
For example, in Notepad, it looks like this: [/color]
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs


2.csv is a test file. It is not an Excel file.
You can open a .csv file in Excel, and Excel will do its best to display the data in columns

Sometime Excel will do this:

_____ Workbook: 2.csv ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE		6			A					GTT
2	NSE		6			A					GTT
3	NSE		6			A					GTT
4	NSE		6			A					GTT
5	NSE		6			A					GTT
6

Worksheet: 2


Sometimes Excel will do this:

_____ Workbook: 2.csv ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	NSE,,6,,,A,,,,,GTT
2	NSE,,6,,,A,,,,,GTT
3	NSE,,6,,,A,,,,,GTT
4	NSE,,6,,,A,,,,,GTT
5	NSE,,6,,,A,,,,,GTT
6

Worksheet: 2

https://excelfox.com/forum/showthrea...sx-to-txt-file
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706


Alert..txt from Avinash : FromAvinashTextFileAlet__txt.JPG : https://imgur.com/HDHgB0z

Code:

---

USA,101010,6,<,12783,A,,,,,GTT,
USA,22,6,<,12783,A,,,,,GTT,
USA,17388,6,<,12783,A,,,,,GTT,
USA,100,6,<,12783,A,,,,,GTT,
USA,25,6,<,12783,A,,,,,GTT,

---

Code:

---

"USA" & "," & "101010" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "22" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "17388" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "100" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "25" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf

---

Code:

---

"USA" & "," & "101010" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," &
vbLf & "USA" & "," & "22" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," &
vbLf & "USA" & "," & "17388" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," &
vbLf & "USA" & "," & "100" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," &
vbLf & "USA" & "," & "25" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," &
vbLf

---

You will see that vbLf is the separator for lines(records)

This is the macro i used to get that infomation:

Code:

---

Sub WhatStringIsInAlertDotDot_txt() '  9th June 2020 https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\Alert..txt" ' This  would be made  if not existing and we would have a zero lenf string
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
Dim Lenf As Long: Let Lenf = LOF(FileNum)
TotalFile = Space(Lenf) '....and wot recives it hs to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
' What the fuck is in this string?
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)  '  'https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page34#post13699      https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11015&viewfull=1#post11015
End Sub


---

---

Here is the macro to answer this thread
https://excelfox.com/forum/showthrea...sx-to-txt-file

Code:

---

' https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file
Sub xlsxTotxt_LineSeperatorvbLf_valuesSeperatorComma()
Rem 1 Workbooks info
Dim Wb1 As Workbook: Set Wb1 = Workbooks("sample2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1)
Dim Lr As Long, Lc As Long
 Let Lr = Ws1.Cells.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Lc = Ws1.Cells.Item(1, Ws1.Columns.Count).End(xlToLeft).Column
Dim arrIn() As Variant: Let arrIn() = Ws1.Range(Ws1.Range("A1"), Ws1.Cells.Item(Lr, Lc)).Value ' Data range in sample2.xlsx
Rem 2 make text file long string
Dim Rw As Long, Clm As Long '
    For Rw = 1 To Lr ' each row in Ws1
        For Clm = 1 To Lc ' each column for each row in Ws1
        Dim strTotalFile As String
        Let strTotalFile = strTotalFile & arrIn(Rw, Clm) & "," ' add a value and a seperator for this line
        Next Clm
    Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1) ' this will take off the last  ,
    Let strTotalFile = strTotalFile & vbLf  ' this adds the line seperator wanted by Avinash  -  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page30#post13348  -  You will see that vbLf is the separator for lines(records)
    Next Rw
 Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1)    ' this takes off the last  vbLf
 Debug.Print strTotalFile
Rem 3 make text file from the  total string
Dim FileNum As Long
Let FileNum = FreeFile(1)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
 Open ThisWorkbook.Path & "\csv Text file Chaos\Alert..txt" For Output As #FileNum  ' CHANGE TO SUIT  ' Will be made if not there
 Print #FileNum, strTotalFile '                      strTotalFile
 Close #FileNum

End Sub

---

( This post is https://excelfox.com/forum/showthrea...ge30#post13349 )

---

Some notes related to these posts

https://excelfox.com/forum/showthrea...rt-Csv-To-Xlsx https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364

---

See here ( This post https://excelfox.com/forum/showthrea...ge30#post13349 )

---

for typical comparisons of text Files, Excel files, and data files
Text File: https://excelfox.com/forum/showthrea...ll=1#post13693
Excel File: https://excelfox.com/forum/showthrea...ll=1#post13694
Data File: https://excelfox.com/forum/showthrea...ll=1#post13695


Excel File
_____ Workbook: Alert..xls ( Using Excel 2007 32 bit )

Excel Files

A

B

C

D

E

F

G

H

I

J

K

1

USA

vbTab

101010

vbTab

6

vbTab

<

vbTab

12783

vbTab

A

vbTab

vbTab

vbTab

vbTab

vbTab

GTT

vbCr

&

vbLf

2

USA

vbTab

22

vbTab

6

vbTab

<

vbTab

12783

vbTab

A

vbTab

vbTab

vbTab

vbTab

vbTab

GTT

vbCr

&

vbLf

3

USA

vbTab

17388

vbTab

6

vbTab

<

vbTab

12783

vbTab

A

vbTab

vbTab

vbTab

vbTab

vbTab

GTT

vbCr

&

vbLf

4

USA

vbTab

100

vbTab

6

vbTab

<

vbTab

12783

vbTab

A

vbTab

vbTab

vbTab

vbTab

vbTab

GTT

vbCr

&

vbLf

5

USA

vbTab

25

vbTab

6

vbTab

<

vbTab

12783

vbTab

A

vbTab

vbTab

vbTab

vbTab

vbTab

GTT

vbCr

&

vbLf

Worksheet: Sheet1