VBA Copy Rows From One Workbook To text csv File Based On Count In Different Workbook. Cross Posted Chaos

[fixer]

Moderator notice.
This is the start of duplicate cross posting chaos
2 months latter he is still wasting his and lots of peoples times going around in circles making a total mess in posts everywhere because he refuses to understand anything at all about Text files.
The biggest problem is in using an Excel object to open a .csv File, which is usually not a good idea.
Sometimes you might get the impression he is understanding at least a small part of the problem, but wither it is just co incidence that what he has pasted infers that and he has no idea what he is writing, or two seconds later he forgets and we are back to the starting point and he starts again duplicating cross posting the same question… - I think there is a good chance the OP is insane, and certainly a total dim pig shit for brains. Physical violence is I think the only hope for him, and if all else fails then he should be put to death.,









vba will be placed in a seperate file macro.xlsm

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

plz see the sample file


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1 (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdsh r6 (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdsh r6)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ- (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9z XI (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9z XI)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_ 3f (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_ 3f)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11 nH (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11 nH)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOe Xa (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOe Xa)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpT G8 (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpT G8)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Remember to always please give a link to everywhere else that you post, for example
http://www.eileenslounge.com/viewtopic.php?f=30&t=34508 http://www.eileenslounge.com/viewtopic.php?f=30&t=34508

If you have posted anywhere else then please tell us where




In the macro I have done for you , there are two possibilities.
You only need one
You can choose
' 2b)(i) Relative formula references ...

' 2b)(i) Relative formula references ... 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
OR
' 2b)(ii) Copy Paste

' 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

Before:
_____ Workbook: 3.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L

1NSE
6AGTT


2
Worksheet: Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1182
1193
1151.7
1190.45
1156.6
22
11.566
116815
1168.166


3NSEADANIENTEQ
137.15
140.55
134.1
140.5
134.65
25
1.3465
13595
135.9965


4NSEADANIPORTSEQ
273.95
276.95
269.55
277.6
270.65
15083
2.7065
27335
273.3565


5NSEADANIPOWEREQ
32.3
32.35
30.45
32.45
30.65
17388
0.3065
3095
30.9565


6NSEAMARRAJAEQ
555
555
529.25
557.85
532.1
100
5.321
5374
537.21


7
Worksheet: 1-Sheet1 3Mai

_____ Workbook: 2.csv ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J

1


2


3
Worksheet: 2


After running macro here : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13184&viewfull=1#post13184

_____ Workbook: 2.csv ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L

1NSE
6AGTT


2NSE
6AGTT


3NSE
6AGTT


4NSE
6AGTT


5NSE
6AGTT


6
Worksheet: 2



Alan


Share ‘1.xls’ : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Share ‘macro.xlsm’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
Share ‘2.csv’ : https://app.box.com/s/sop0hl5slstjv38tr8wtvp7f7l39ep3k
Share ‘3.xlsx’ : https://app.box.com/s/8k9b556duom523b381m80oeixonp0042




Edit ... cross posted later once again!!!!
https://chandoo.org/forum/threads/copy-and-paste.44182/
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=13439&viewfull=1#post13439

[fixer]

Thnx Alot Doc Sir for showing ur Great Interest in this post
Sorry for not giving a link bcoz i thought no one is working on this problem so Time will not be wasted of anyone
I will take care of the same in future

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\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
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = CL(Lc3)
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues
w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

End Sub


i used this code Doc Sir
plz see the result i am getting i have uploaded the pic of the same
So plz relook in the same sir




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

What does 2.csv look like before you save and close it
What does this do:

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\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
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = CL(Lc3)
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues

End Sub

What does 2.csv look like after running above macro

[fixer]

I have uploaded the pic plz have a look sir
all the data is pasting to column A in one cell

[DocAElstein]

This may be a complicated issue. ( I also said this here: ( https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13150&viewfull=1#post13150 ) )
This may be an issue with csv and Excel.
Your Excel will do csv different to my Excel. It is a very complicated issue. In English it is difficult to explain.
To explain to you will be almost impossible.

Your Excel may be doing different to my Excel with csv.
Maybe it is related to this question and problem : http://www.eileenslounge.com/viewtopic.php?f=30&t=34497#p267711




I would not manipulate csv files with Excel. I would do it like with Read and write files to text files
( https://www.homeandlearn.org/open_a_text_file_in_vba.html
https://www.homeandlearn.org/write_to_a_text_file.html )



I am not sure if I can help you further.

I would need to see your csv file.
I would need to know exactly what you want to do

I think the communication problem between us would make it impossible to take this issue further.
The problem is too complicated for your understanding of English.

Alan

[fixer]

we have to use Chr(160) i think so
Plz have a relook Doc Sir
it should copy the complete row and it should paste the same
see the pic in capture.png which we are copying
and see the data in result pic how it is pasted

[DocAElstein]

Once again …
This may be a complicated issue. ( I also said this here: ( https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13150&viewfull=1#post13150 ) )
This may be an issue with csv and Excel.
Your Excel will do csv different to my Excel. It is a very complicated issue. In English it is difficult to explain.
To explain to you will be almost impossible.

Your Excel may be doing different to my Excel with csv.




we have to use Chr(160) i think so
I do not understand what Chr(160) is. I do not understand how to use it. To use Chr(160) here may be an issue with csv and Excel.
Your Excel will do csv different to my Excel. It is a very complicated issue. In English it is difficult to explain.
To explain to you will be almost impossible.



I myself, I would not manipulate csv files with Excel. I would do it like with Read and Write files to text files
( https://www.homeandlearn.org/open_a_text_file_in_vba.html
https://www.homeandlearn.org/write_to_a_text_file.html )



I am not sure if I can help you further.

I would need to see your csv file.
I would need to know exactly what you want to do

I think the communication problem between us would make it impossible to take this issue further.
The problem is too complicated for your understanding of English.
This would take me many days , if I have the time later, in a few days.

I will need to
Have two .csv files
_ One for Before
_ One for After
They must be .csv file. ( Maybe try GoogleDrive , app.box.com or other file sharing service.)
I must have files . csv files for Before and After

Screenshots are no use.



As always, the VBA problem and solution may be simple
The big problem is communication between us.
I mostly waste my time writing , because you understand almost nothing that I say !!


Alan

[fixer]

Before csv file link https://drive.google.com/open?id=1MFIgUUiH0QPO1oWpDms6EWCLjkblGxfo
after runing macro csv link https://drive.google.com/open?id=1V_85p1O4lV4RvqHw1dS63idSd5zlDcVX

[sandy666]

there is chr(9) not chr(160)

[fixer]

Sandy Sir can u share the complete code with correction
bcoz i am confused with this code, i tried to make many changes but i was not successful
So its my request i am sending u the code plz have a look in it and plz let me know about the correction or correct the code and share the same
i am using this code

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\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
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = CL(Lc3)
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues
w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

End Sub
.

[sandy666]

sorry but no. I am away from vba.
I checked csv only, that's all

[fixer]

Ws2.Cells.NumberFormat = "General"
I changed this to

Ws2.Cells.NumberFormat = Chr(9)
But i am not getting correct output sandy Sir

[sandy666]

chr(160) or chr(9) are delimiters not a format

[fixer]

Sorry for the same
Plz let me know sandy sir what correction i want to do

[sandy666]

My dear friend, as I said I am not able to help you with vba
You need to describe your problem with details, what you want to do, post representative example (excel file and source(s) )what you have and what is your expected result
then maybe any master of vba will help you %D

[fixer]

Problem is already described in the post sandy sir plz relook

[sandy666]

2895
is that what you want?

note: remove .txt to get result.csv only

[fixer]

Bro i am not getting what u mean to say i tried to correct it from many ways but i was unsuccessful
if there is any changes or correction plz correct it and share
i am not having enough knowledge of vba tp solve this problem

[sandy666]

I am not talking about vba but asking question: the file i posted above is in format you want to achieve?

[fixer]

Plz see the sample pic Sir
i need this result after runing the macro AFTER RUNING THE MACRO.PNG Sir

[sandy666]

B
C
D
E
F
G
H
I
J
K
L

2NSE
6AGTT


3NSE
6AGTT


4NSE
6AGTT


5NSE
6AGTT


6NSE
6AGTT

I forgot to remove last column with numbers
2899

this will be better because doesn't matter place where csv will be after open in Excel

NSE
6AGTT

NSE
6AGTT

NSE
6AGTT

NSE
6AGTT

NSE
6AGTT


out of curiosity: why result in csv format?

[fixer]

Bro what i have to do with this result.csv.txt
?

[fixer]

Sub Step14() ' 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("1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xlsx")
Set w2 = Workbooks("2.csv") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\document\2.csv")
Set w3 = 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
' w1.Close
' w2.Save
' Let Application.DisplayAlerts = False
' w2.Close
' Let Application.DisplayAlerts = True
' w3.Close
'
End Sub

This is the vba code Doc Sir gave me
Now plz have a look into this code and if any changes are required then plz let me know

[sandy666]

change result.csv.txt to result.csv and open with eg.Excel
btw. csv is NOT a native Excel format but IBM

I added txt on the end because this forum don't accept csv files

edit:
csv = comma separated values

[fixer]

I dont need csv files i need a macro that will do the process and i will get the result what i am looking for
i have a vba code that is slightly different but that code has pasted the data to csv i am sharing the same
dont get confuse i am sharing the code only to understand the probelm and solve this problem

Sub STEP3()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim strPath As String
Dim R As Long
Dim m As Long
Dim rng As Range
Dim n As Long
Application.ScreenUpdating = False
Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
Set ws1 = wb1.Worksheets(1)
m = ws1.Range("H" & ws1.Rows.Count).End(xlUp).Row
strPath = ThisWorkbook.Path & "\"
Set wb2 = Workbooks.Open(strPath & "OrderFormat.xlsx")
Set ws2 = wb2.Worksheets(1)
ws2.Range("A1:A4").TextToColumns DataType:=xlDelimited, Tab:=True, _
SemiColon:=False, Comma:=False, Space:=False, Other:=False, _
ConsecutiveDelimiter:=False
Set wb3 = Workbooks.Open(strPath & "BasketOrder..csv")
Set ws3 = wb3.Worksheets(1)
Set rng = ws3.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If rng Is Nothing Then
n = 1
Else
n = rng.Row + 1
End If
For R = 2 To m
If ws1.Range("H" & R).Value > ws1.Range("D" & R).Value Then
ws2.Range("A2").EntireRow.Copy Destination:=ws3.Range("A" & n)
n = n + 1
ElseIf ws1.Range("H" & R).Value < ws1.Range("D" & R).Value Then
ws2.Range("A4").EntireRow.Copy Destination:=ws3.Range("A" & n)
n = n + 1
End If
Next R
Application.DisplayAlerts = False
wb1.Close SaveChanges:=False
wb2.Close SaveChanges:=False
wb3.SaveAs Filename:=strPath & "BasketOrder..csv", FileFormat:=xlCSV
wb3.Close SaveChanges:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[sandy666]

sure, so I can't help
I did what you said in the first post without vba
have a nice day

[fixer]

That's Great Bro I like that & thnx for the same
Bro but i need that to be done by vba code this is my question & this is i am looking for
Thnx for ur Great Help & I like ur Attempt to solve this Problem
Relax Bro It will take time but i will resolve this problem & i will share the code with u
Have a Great Day

[fixer]

Doc Sir u have mentioned some code in this link https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13184&viewfull=1#post13184
i saw that but i am confused what i have to do now with this problem what exactly the code should be as per u

[DocAElstein]

I am trying to answer your question. I am working on it - I am developing a solution in the Development Test Forum at that link
Wait

[DocAElstein]

Hi Sandy ;)
Thank you Mr Sandy, Sir, for your interesting contribution to this Thread.,
I expect vixer has not understood much, due to his appalling grip on the English language, but it has given me some interesting insights into the issues involved.

I have also tried to explain to vixer that we are dealing with issues of Excel and how it handles files of text / csv etc type.
I am not too clued up on these issues, and vixer usually has no idea half the time of what the hell is going on at the best of times when he is only looking at Excel VBA issues. As he is now including extra issues to do with csv / txt files , it is anyone’s guess what he is wanting. I doubt very much he knows himself very clearly. Coupled with his inability to explain what he wants in English, it is anyone’s guess what is wanted here in this Thread.

Never mind.
In the next post I will try to take the Thread a bit further.
Then I will go off and hide in a hole in my garden, Chisel out a few more rocks, ( https://imgur.com/8mV1o8q ) , and pull some of my teeth out for relaxation.

Then tomorrow I will look in again here to see what’s happening

Alan

[DocAElstein]

Hello Vixer
As always, the main problem is that you either cannot explain what you want, or don’t understand yourself what you want or what you are doing. Probably a bit of both.

Never mind, stay cool Bro, an lets move on…. :)

I have tried to do some analysis of your 2 supplied “csv files” Before and After , here : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13208&viewfull=1#post13208

I am probably wasting my time trying to explain what Sandy also is trying to explain…
csv means
comma separated values
__ , __ separated values
https://en.wikipedia.org/wiki/Comma-separated_values
Your supplied files ( From here: https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13193&viewfull=1#post13193 https://drive.google.com/file/d/1MFIgUUiH0QPO1oWpDms6EWCLjkblGxfo/view https://drive.google.com/file/d/1V_85p1O4lV4RvqHw1dS63idSd5zlDcVX/view ) are not csv files
I do not know what they are. Maybe we can call them Tab delimited or Tab separated. Or we can call them dog shit files

I will now modify my last macro so that it makes the DogShit file that you want

The macro will produce two files:
2.csv
DogShit.txt

We do not open 2.csv or DogShit.txt … We make them !
Do not open 2.csv in the macro!!!! Do not put a code line in the macro to open 2.csv!!! ( You can open manually later with text editor or Excel )

Here is the macro : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13217&viewfull=1#post13217
After you run that macro , you will see two new files:
New made Dog Shit files.JPG : https://imgur.com/UUZ09z6
2902

Those two files, which my latest macro produces, are identical to your supplied After csv file
After csv.JPG : : https://imgur.com/IzaxRrh
( https://drive.google.com/file/d/1V_85p1O4lV4RvqHw1dS63idSd5zlDcVX/view )
2903


I must go now , dig a big hole and pull some teeth out for relaxation ( https://imgur.com/8mV1o8q ) ,
Tomorrow I will look in again here ….

Alan





Share ‘1.xls’ : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Share ‘macro.xlsm’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
Share ‘3.xlsx’ : https://app.box.com/s/8k9b556duom523b381m80oeixonp0042

[sandy666]

Hi Sandy ;)
Thank you Mr Sandy, Sir, for your interesting contribution to this Thread.,
I expect vixer has not understood much, due to his appalling grip on the English language, but it has given me some interesting insights into the issues involved.


Hi Mr Alan Sir ;)
I gave up %p
---
I did it with M-code but I really don't know what fixer want as the result :confused:


// Sheet1
let
Source = Excel.Workbook(File.Contents("D:\test\csvs\3.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
List = Table.AddColumn(Sheet1_Sheet, "Custom", each {1..CNT}),
Expand = Table.ExpandListColumn(List, "Custom"),
RC = Table.RemoveColumns(Expand,{"Custom"})
in
RC

// CNT
let
Source = Excel.Workbook(File.Contents("D:\test\csvs\1.xls"), null, true),
#"1-Sheet2" = Source{[Name="1-Sheet1"]}[Data],
Promote = Table.PromoteHeaders(#"1-Sheet2", [PromoteAllScalars=true]),
Count = Table.RowCount(Promote)
in
Count

[fixer]

I want this in a csv file not in a notepad
I am looking for automation of the same
Why we should waste our time to transfer data from notepad to excel
Its not a new thing i am asking for i already have a macro who paste the data to same csv file but it is slightly different
I think lets make it simple
the work of macro is counting and pasting the data to csv not more then that its not a new thing
I am not that much educated so i cant solve it but u can solve
the same (as sandy said masters of vba)

[sandy666]

csv is a text file with comma separated values. it can (but not must - this is not mandatory) be opened in notepad (text file) or Excel or any other program which accept this kind of file
as I said before: csv is NOT native format of Excel

it's a bit weird you want create csv from Excel then open this csv in Excel. Would be better to get the result directly in Excel, isn't it?

FYI: CSV (https://en.wikipedia.org/wiki/Comma-separated_values)

[fixer]

I completely agree with all terms and conditions u r saying
I only mean to say that in csv file can we use the macro to copy & paste?
1)If yes then for this problem we can do the same
2) If No then I have a macro that has pasted the data to csv (slighlty different from this problem)
3)one more thing no doubt it was a csv file i have save that file in csv (If u have a doubt that it was not a csv file or any other xyz file then u can create a csv file and take 1.xls &3.xlsx from the post and run the macro and see the result

[fixer]

Let's leave this topic
We will solve the problem in a different way
Thnx Sandy Sir & Doc Sir for helping me in solving this problem
Have a Great Day Bro

[sandy666]

what does that mean: in csv file can we use the macro to copy & paste ?
to me it looks like any vba code inside csv file, like generate vba from vba to csv
usually file with vba is a .bas file (also in text format, which not means it must be open in notepad)

I generated csv with the result like you want, it could be open in Excel with data format you want. Of course I did it without vba so Alan will be better to create vba for this

if you want csv , generate the result you want by vba in Excel then use vba to save this excel as csv

[fixer]

So plz provide me that line so that i will put the same and it will be solved sandy sir

[sandy666]

So plz provide me that line so that i will put the same and it will be solved sandy sir

if you want do this via vba you need to wait for Alan, or someone else

[fixer]

No Doubt Bro Obviously i am waiting for Alan Sir Only
But i dont want to irritate Alan bcoz he has helped me alot
So meanwhile i am thinking about the different ways to solve the same bcoz if Alan was unable to do the same then i will require a
a different strategy to solve the same

[sandy666]

different way? post#33 - Power Query

[fixer]

No we will solve the Problem with 2 macros
Bcoz we will break the process into 2 pieces one macro to count total no. of rows
& second macro for copy paste to csv
It will be easier to understand & easy to solve

[DocAElstein]

... i am waiting for Alan Sir Only
But i dont want to irritate Alan bcoz he has helped me alot
So meanwhile i am thinking about the different ways to solve the same bcoz if Alan was unable to do the same then i will require a
a different strategy to solve the same
I cannot solve a problem if I do not know what the problem is.
I cannot answer a question if I do not know what the question is.
The problem in helping you is always the same. It is not VBA Excel problem. Problem is you are not English. You do not understand English language. Problem is always you and English language.

You ask Question A - We give Answer A
You ask Question B - We give Answer B
Usually, those are not the answers that you want...
You want Answer F - But you cannot speak English. You cannot understand English. You cannot write English. You do not understand English language.
You try to ask question F. But you fail. You ask any question. You ask always wrong question. You write mostly rubbish.

We must keep trying to guess what you want



If you want more help from me – Do all this:
_1) Read this : https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13222&viewfull=1#post13222
_2) Read this : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13208&viewfull=1#post13208
_3) Run macro Sub Step14_DogShit() https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13217&viewfull=1#post13217

_4) Now tell me what that macro does for you? - What results do you get ?

_.____

You have the whole day , Today , to do this all. Take your time. Do it all. Read it all. Do it slowly. I will not answer any more here today. This is my last post here today.
I will look again tomorrow.



If you cannot understand what we write, and if you do not do what we ask, then only God can help you. God can see inside your head. God knows what is your question. We are not God. We cannot see inside your head.

[fixer]

U haven't understood the problem
but u have mentioned something plz see the sample pic of the same Doc Sir
U can run the macro and u can see the output is this the same out put which u get after runing the macro which u mentioned in the pic
No Probelm Doc Sir
i am breaking the process in two parts
1)first macro for counting the rows
2)second macro for copy paste

[sandy666]

the problem is because you trying open csv (result) by double click which is not a proper action
you should import csv with defined delimiter (tab)
with double click, Excel ignore tab as delimiter and you've all data in single column

as i said this is a comma separated values type of file and if you define delimiter in your vba comma instead of tab it will work with dbl click or just import

Alan did his job great with your requirement and you got csv with copied data, but what will you do with this csv (and problems with that) is on your side

regards

[fixer]

sandy sir plz share the complete working code if u know
Bro i am not that much smart in vba
U r giving me the hint
But i dont know much about vba
I am not a Vba coder
My dream is to become a Speculator
& without u I can't create a system & if i am unable to create a system then i cant achieve my dream

[sandy666]

I think you got a vba code in the first few posts from Alan
the code generate result with tab as delimiter but you trying to open this csv in Excel by double click not import with defined delimiter (tab)
you got what you want but you didn't say what you want to do after vba generate csv file

you should use this option if you want "open" csv with tab delimiter in Excel
2907

[fixer]

No i want to make it automatic, vba code should do all the work
I will not do any manual work

[sandy666]

so you don't need step(s) with csv
just create vba to generate result directly in excel

[fixer]

Bro No Doubt in it I have created the same bro
But i am not having knowledge about vba
I am learner
in 9 months I cant be a masters of vba

[sandy666]

so ask Alan to create vba with the result in worksheet not csv

and don't ask me about vba
I just tried to explain some rules in Excel

[fixer]

Chill Sandy tc & have a great day

[fixer]

Trying to explain the problem

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\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
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = "K"
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues
w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

End Sub
this code provides incorrect output in csv file
oncorrect output is attached plz see

[fixer]

the correct output is uploaded plz see i need this result in csv

[DocAElstein]

My dream is to become a Speculator ...
This will be very difficult for you to achieve your dream if you cannot speak English. In this world, you will not achieve very much if you cannot master the English language!!


The biggest problem remains. You are incapable of communicating with us. You either ignore us, or you do not understand most of what we write and do for you.
You are totally incapable of telling us what you want.
It is impossible for us to understand you, because you can not communicate in English.


Sandy is probably getting close to guessing what you want: He is very clever, he can almost read your mind…
Your current question seems to be related to opening a file with Excel.
Your current question seems to be related to opening a text file and displaying it in a particular format in Excel
But I am not sure.
Everything that you write makes no sense in English.



For today, I will just try again to explain what is different file types here:
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13248&viewfull=1#post13248
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13247&viewfull=1#post13247

That is all I am doing for you today


This is all that I am doing for you today.
Tomorrow I will look in again to see if you have managed to tell us in English language what you want.

Alan

[fixer]

Important plz see this post & i tried to mentioned all details correctly( easy to understand ) plz see
& plz create a 2.csv file from ur end bcoz it is completely blank and in the forum i cannot upload the 2.csv file, so create a csv file & name it 2.csv file

[sandy666]

Important plz see thi post i tried to mentioned all details correctly easy to understand
plz see
& plz create a 2.csv file from ur end bcoz it ia completely blank and in the forum i cannot upload the 2.csv file
so create a csv file name it 2.csv file

Could you write posts without slang shortcuts?
Maybe this is a reason why we don't understand what are you trying to achieve
so...

btw. to me Ur was Sumerian city-state in ancient Mesopotamia

[fixer]

I corrected the same sandy sir
now run the macro and see the output
have u got the output as attached in that post(Correctoutput.png)?
Now there are 2 ans of this question
1)Yes(yes is not possible bcoz that macro has issue with pasting of the data)
2)No(If No then correct the macro so that it can provide me the desired result)

[sandy666]

I corrected the same sandy sir
now run the macro and see the output
have u got the output as attached in that post(Correctoutput.png)?
Now there are 2 ans of this question
1)Yes(yes is not possible bcoz that macro has issue with pasting of the data)
2)No(If No then correct the macro so that it can provide me the desired result)

I see you did not understand.
use: you, answers, because, please, your, yours and so on...

[fixer]

ok bro but you understood what i mean to say

[sandy666]

I am not sure

btw.
Bro (novel), a novel by Vladimir Sorokin
Bro (TV channel), a Philippines TV network
Bro (instrument), a Vietnamese musical instrument

as I said, try to not use slang and mental shortcuts or street language

[fixer]

No Doubt Macro is not correct
macro saved the file as tab delimited & i dont want that to happen it should saved the file as xlcsv
So plz correct the macro Sir

[sandy666]

post#54
change line

Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
to

Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", "",""))")

I really don't know what I did but it works

CSV

2914

and after open in Excel


NSE
6AGTT

NSE
6AGTT

NSE
6AGTT

NSE
6AGTT

NSE
6AGTT

[fixer]

Sandy Sir Thnx Alot, now i ran the macro and i saw the details
Now the issue is with file saving method

w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close
'
End Sub

Something is causing mistake from these lines plz have a look Sandy Sir
some changes is required in this
w1 and w3 is safe, issue is with w2 plz see

[sandy666]

as I said before: plz = PLEASE ! so use it

I used the same code with changes from post #65 and I don't see any problem
You even didn't show any error

and again: I DON'T KNOW VBA so don't ask me about it
Hope you understand plain English

[fixer]

Yes i also saw the correct output but when i save the files and opem it again then i am getting incorrect output

[sandy666]

2.csv should be blank or contain last proper result, if not run macro again without any changes

but why this is happening I don't know

ask author of this code

[fixer]

sure sandy Sir

[fixer]

The only problem is with saving of the file
Macro is doing perfect job, but when it comes to saving then there is an issue
Problem start with this line Doc Sir
[/CODE]w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close
'
End Sub[/CODE]
ignore w1 & w3 they are not csv file, w2 is the csv file
W2 saving line has some issue Doc Sir Plz have a look on that

[fixer]

ws2.Range("A1:A4").TextToColumns DataType:=xlDelimited, Tab:=True, _
SemiColon:=False, Comma:=False, Space:=False, Other:=False, _
ConsecutiveDelimiter:=False

this is just a sample only
If saving the file doesn't work then in our code this type of statement can resolve the issues

[DocAElstein]

now run the macro and see the output
have u got the output as attached in that post(Correctoutput.png)?
Now there are 2 ans of this question
1)Yes(yes is not possible bcoz that macro has issue with pasting of the data)
2)No(If No then correct the macro so that it can provide me the desired result)
This is incorrect
There are 3 possibilities
1)Yes(yes is not possible bcoz that macro has issue with pasting of the data)
2)No(If No then correct the macro so that it can provide me the desired result
3) fixer has understood almost nothing of the issues, and he is almost totally incapable of explain anything, or communicating anything in English.

The answer is 3). The answer is almost always 3)





I think I am slowly starting to understand the question and issues which you have been trying to explain to us……

On the 27th April, I told you to check this: https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13152&viewfull=1#post13152
( That macro back then gave you the comma seperated values text file output )
You did not understand or you ignored it. Because you cannot understand us or you ignore us....._
_.....It is now 8th Mai. It is now almost 100 posts later ….



The issues is not any macro I gave you.
All macros do what you asked for.
The issue is not saving any file. All files were saved in the format you wanted. ( You have asked for many different formats, all have been given as you asked and all gave correct output. All macros given to you gave the results that you asked for. All gave the correct results. )

Your issue appears to be with opening. ….. On the 27th April, I told you to check this: https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13152&viewfull=1#post13152



Question: ( This is my latest guess at what you are trying to ask for )
I have a comma separated file,( for example, from yesterday, : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13247&viewfull=1#post13247
‘CommaSeperatedValues.txt’ : https://app.box.com/s/qcjpeu0vt875513gqawmtoufeba3xb28
‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq )

When I open this using Excel, I want the comer separated values to appear across the rows, with one value in each cell. I do not want all the values for one row to go in a single cell.


Answer:
This question is a very common question. There are very many solutions to this. This has been done very many times. It can be done manually in many ways.
It can be done with VBA in many ways.
It is a very easy answer and solution

Here is just one solution :

_1) First you must try to understand the issues , you must try to understand what is csv. Otherwise all solutions will be of no use to you.
CSV, comma separated values files (FYI: CSV (https://en.wikipedia.org/wiki/Comma-separated_values) )
A comer separated values file is nothing to do with Excel… ( Sandy has already tried to explain this to you many times)
It is a text file. It is just plain text. It can be xxxx.txt or xxxxx.csv.
It can have only plain text. It cannot have any other information. It cannot have any information to tell Excel what to do with it.
Excel may open it in different ways. Your Excel may do that differently to other peoples Excel. ( I already tried to tell you this many times :
https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13150&viewfull=1#post13150
https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13189&viewfull=1#post13189 )
( You can change your Excel settings manually, or do other things manually to get the comer separated values displayed as you want them, as Sandy has also already explained )

_2) Macro example to open a comma separated values text file:
For example, here are some test files, from yesterday, : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13247&viewfull=1#post13247
‘CommaSeperatedValues.txt’ : https://app.box.com/s/qcjpeu0vt875513gqawmtoufeba3xb28
‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq

Here is a macro that will open them : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13272&viewfull=1#post13272

( You will need to have the comma separated values text files, CommaSeperatedValues.csv and CommaSeperatedValues.txt in the same place as the file in which the macro is. )

After running the macro , Sub OpenTxtFiles_ValuesToBeSeperatedIntoExcelCells() , the values will be placed in the first worksheet of the file in which the macro is in:
Results After:

_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H

1zyxw123jhasriderrogeranjussumanjjj


2Leonardoumpsbugkinjalstinamishrakinjal124WigWam


3
Worksheet: Tabelle1





Share ‘macro.xlsm’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
‘CommaSeperatedValues.txt’ : https://app.box.com/s/qcjpeu0vt875513gqawmtoufeba3xb28
‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq






I don’t have any more time for you here today.
Please read carefully this post and my next post, and also the PM I try to send you…

Alan

[DocAElstein]

fixer, ( rider , roger, RangeRover , Leonardo , umpsbug, xyz1234, Pftang Pftang OLE WigWam Biscuit Barrel, Leonardo1234, rider@1234, jhas56788, kinjals, andy124 , tinamishra, ANJUS, kinjal124 , sumanjjj , zyxw123 et al. )

On the 27th April, I told you to check this: https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13152&viewfull=1#post13152

You did not understand or you ignored it. Because you cannot understand us or you ignore us.
It is now 8th May. It is now almost 100 #posts later
In this time I have spent for you , I could of answered 20-30 forum questions. This much longer time needed to help you is only needed because you cannot explain in English.

All your questions are very easy to answer. You could ask 1000 of your questions, because they are so simple questions, you would then get an answer to all your questions very quickly. All your VBA questions can be answered very quickly .

BUT BIG Problem is
You cannot understand English.
You cannot write English
You cannot communicate in English

You waste everybody’s time. Your problems need just a few minutes to ask and answer.
But it needs us many weeks for us to guess what you want. You cannot explain or understand anything in English

You almost never understand what we tell you.
You are almost always totally incapable of explaining in English what you want, or what the problem is.

_._____

We are much more tolerant at excelfox then anybody else. You know this. Everywhere else has already stopped helping you.
But this cannot go on here. You are wasting our time and you are wasting your time.
This is all because you cannot communicate in English

If , in future , you cannot communicate effectively your question, then I must reluctantly Ban you. This will be in everybody’s best interest, including you.
If I Ban you, then you are Banned. This means that you can get no more help from excelfox


Alan

[fixer]

Problem Solved
Thnx Alot Doc Sir and Sandy sir for helping me in solving this problem
Have a Great Day

[fixer]

Sorry this will not be repeated in future

[fixer]

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\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
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = "K"
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues
w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

End Sub

u gave me this macro and when i ran it the same problem occur which i have mentioned
and u gave me one more code by not opening 2.csv in the macro that creates data in notepad but bro cant it be done by a single macro
I always ask a problem to u and u provide me one macro that does all the work & in this problem also i need a single macro that will do the work
& Sorry again if i failed to understand what u mean to say

[fixer]

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\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
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = "K"
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues
w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

End Sub

with this macro
there is one issue
actually the file is been saved with text(tab delimited) and that's y i am getting that type of incorrect output
If the file is been saved as csv format then everything is perfect
I dont know excel is causing that issue or what is causing this issue that this is been saved as text(tab delimited) but if we do something in the macro that it save the file in csv format instead of text(tab delimited) then this will be very helpful to me and this problem will be solved
I request u to plz have a look and do needful

[DocAElstein]

with this macro
there is one issue
No.. There is at least one more issue… https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13185&viewfull=1#post13185
You can choose… ' 2b)(i) Relative formula references ... … or ' 2b)(ii) Copy Paste...
This is not a major problem, It is not a major issue. But once again it shows that you do not read what is written , or you don’t understand simple English

The following continues to be your characteristic:
You look very quickly just for a macro in a post. You run it. If it does not do what you want you just write a few word and/ or post a bad screenshot, with no , or incorrect or useless short explanations, and then hope for another macro to try. This repeats on and on until you either get a working macro or you give up or you get a macro from somewhere else, and probably won’t tell us always.
We all waste our time. It is not helping you either.




..actually the file is been saved with text(tab delimited) and that's y i am getting that type of incorrect output…
No. The output is perfect. It is exactly what you asked for this time. ( Previously you already asked for comma seperated values output, and we gave you that as well. The remaiining issues seems to be in opening the file, as we have tried many times to try and discuss with you. But it is proving to be impossible to communicate with you )




…… if we do something in the macro that it save the file in csv format instead of text(tab delimited) then this will be very helpful to me and this problem will be solved…
No. We will then be right back at the start from 2 weeks ago , and we will go around in circles for ever. On the 27th April, I told you to check this: https://excelfox.com/forum/showthread.php/2461-copy-and-paste-by-vba?p=13152&viewfull=1#post13152
I wrote just a few simple English words there. You either did not read them or can’t understand almost any word of English what so ever.



...I don’t know excel is causing that issue or what is causing this issue....lMe and sandy have explained to you many times in great detail what all the issues are.
You sometimes reply to us very quickly , your replies are so quick that it can not be possible for you to have read or understood anything that we have tried to explain.



It is not helping you anymore, because you seem to have some serious mental problem. It appears as if you make no attempt what so ever to understand anything. You don’t seem capable or willing most of the time to even try to explain what you want. We have to keep guessing every time what you want.
It appears almost as if you are trying as hard as you possibly can not to understand anything at all. That is fine for you – Do anything you want to do. But don’t do it here,… when you ask for help and people try to give you help that you then seem to make no attempt to take.




...I always ask a problem to u and u provide me one macro that does all the work & in this problem also i need a single macro that will do the work...It is always very difficult to know what you want because of the communication problem. This time it is proving impossible… I said all this many times before https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13189&viewfull=1#post13189
I have a better chance of guessing what you want if I have no information from you. Your explanation and communication is appalling!
We still don’t know the issue, or what you finally want, because you are incapable of telling us in English!!!!
You sometimes give a screenshot but are not capable of telling us what that screenshot is referring to.!!!



This madness has gone on long enough, You are hurting and damaging yourself, as well as those trying to help you.
I am banning you for one week.
I may try to post some more in the Thread to tidy it up. I might even try to answer your question further, if I can guess what it is you want. But you posting is helping no body. So the ban is primarily intended to stop you doing that, because that is hurting all of us.
You can , and should read your Threads. This seems to be one of your major problems. You read or understand nothing. You look quickly for a macro. You sometime don’t even manage to do that properly: Sometime in the past you have not even seen the posted macro and have completely missed a full working macro and answer
I will continue to help you later, if you wish. I will try to post more in this Thread also, this and next week

This is an exceptional situation: I have a better chance to guess what the problem is, and what you want, than if you post , - you posting is confusing the situation and doing more damage to everyone.

I expect you will not even read or understand anything that I am saying here.

I can only hope that the banning might have some small chance of waking up a brain cell. It is a one week ban. Please do not re register with , or use, any other account in this time.

If you cause me or anyone else any more grief again, then I may have ban to you permanently.
You are banned now, initially for 7 Days, until Saturday 16th May, 2020
This ban is to help us all, including you. You must try to at least read and understand a little.

It is true that nobody has any right to insist that you learn VBA. But it is madness to try to stay permanently ignorant about everything and pay no attention what so ever and make no attempt to understand anything and/or ignore all that is written for you..

In short ... Read your F##king Threads, Bro

Alan

[DocAElstein]

….lets come to pasting of the data
since we know that 2.csv is a csv file, macro should paste the data clearly correctly , not incorrectly
correct way of pasting the data ..…and no notepad data….
I think this indicates a continued mis understanding. 2.csv is not a csv file. It is a text file. It is not an Excel File. It is not a csv file
The term "csv file" does not have a precise definition. It is a general term used very loosely in general discusions. Its use can be misleading.
Sometimes when we say csv file , we really mean to say that it is a text file, and it has comma separated values like
4,g,,tz,hk
- here the values that we want are
- 4 - g - - - - tz - hk -
and in the file, we include commas , to separate them. The comma separators are included to help us not make a mistake like
- 4g - tzh - k




File types: Excel and Text

If we see that 2.csv is a file like xxxxx.csv , then we know that it is a Text file, or text document….
.csv = Text file, not Excel File
2.csv is a text file. It cannot be an Excel file. Because there are no Excel xxxxx.csv files. Not anywhere in the Universe. God cannot make a xxxxx.csv Excel file. It is not possible to make an Excel xxxxx.csv

Excel Files .xls .xlsx .xlsm .xlsb
These are some of the file extensions indicating that we have Excel files: .xls .xlsx .xlsm .xlsb
These are usually opened and viewed in Excel. ( They can also be sometimes viewed in some other software, such as OpenOffice)
These are all Excel Files: Excel Files.JPG : https://imgur.com/yZO6h1R
2916

Text Files .txt .csv
These are some of the extensions indicating that we have text files, ( text documants ).txt .csv
These are usually viewed in Notepad. ( Sometimes they are called Notepad files ) ( They can also be sometimes viewed in Excel, but this is not a good thing to do. It is not a good thing to do, because Excel will display the text in different ways. Your Excel may display them differently to my Excel. Sometimes the values from the text file may be spread across many columns. Sometimes all values will be placed in one column. If a text file like xxxxx.csv or 2.csv or xxxxx.txt is saved using Notepad or Excel, all formatting will be lost: Only text is saved. )
These are all text files: Text Files.JPG : https://imgur.com/9I5nBGm
2917

In that last screenshot there are no Excel files. ( The Typ name… Microsoft Office Excel Comma Separated Value ….. is possibly indicating that the text files of extension type .csv can be opened with Excel. But this is also true of the text files with the .txt extension. Microsoft have been stupid to not show all as Text files, or text documants )
For example, 2.csv is not an Excel File
If I save 2.csv using Excel then it will be saved as a text file.
2.csv is a text documant . It can not be anything else. It can also be called a notepad file. It can never be called an Excel file

If I try to open 2.csv in Excel, using Excel, then I may have issues such as discussed in this Thread.





A guess form me is that fixer is thinking that 2.csv can be saved as a comma separated values file and that it can be saved in such a way that by the next opening with Excel it will be formatted in a specific way. This is not possible, because 2.csv can only have text. It does not have cells. It can never look like the results shown by fixer. Those results are for the text being shown in Excel

The following statement is nonsense:
….” the correct output is uploaded plz see i need this result in csv …..”
ThisCanNeverBeTheResult.JPG : https://imgur.com/5UJ1nUL 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=13246&viewfull=1#post13246
2918

That can never be the output in a file with the extension of .csv
A file with the extension of .csv can never have any format. It cannot have cells. It is not an Excel file. It can only contain text.
( That text can contain , ; vbTab chr(9) chr(160) chr(88) vbCr vbLf etc. , as well as all normal text and numbers 1234 hsge xyz1234 dhqdh1387ubgrf(&(tg*'#+ etc )






Possibly tomorrow I will make some attempt at a full macro to do "everythnig". But this will require some thought, as I will need to somehow geuss what the asker will finally really want, way into the future, when he finally understands all the issues properly...

[DocAElstein]

Issue of Opening a .csv file .
The misunderstanding and misconceptions of what a .csv file is, are causing all the confusion in this Thread.
Some second guessing of what the further requirement of the project to which the question in this Thread is referring to may help.

The so called “wrong results” are almost always demonstrated by a screenshot which can only be obtained when opening a file, after the macro has produced it. So the issues is not actually related to any of the given macros.
This fact , and any attempt to explain this is/was totally lost in the communications problems encountered

It is a good guess that there remains the misconceptions that a .csv file produced by any macro is somehow an Excel file, and so formatting seen in it when viewing that file in Excel will still be there when the file is later opened. This is false. A .csv file is not an Exel file and on opening all formatting will be lost.
In our case the formatting that is being lost is the positioning of values , one in each cell. On later re opening of the file, we may see all values, as well as the separator , ( whether it be a comma or something else ) all in one column.





Second guessing at the larger picture of what is wanted…

I will add some more here tomorrow…..
( I wait to see if there are any clues from this http://www.eileenslounge.com/viewtopic.php?f=30&t=34610 )

Some interesting things form that post. It seems possibly that you can possibly “fiddle around” to arrange that VBA opens text files in a certain way… or other issues....
( Some notes here https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13343&viewfull=1#post13343 )

[fixer]

Problem Solved Doc Sir & Sandy Sir
Thnx Alot for ur Great Support
I got the macro that does the same

[DocAElstein]

Please can you post us the working macro, ( and could you please give us test data files*** which work with that macro )
Please explain to us what the issues were, and please explain to us how those issues have been solved
( Remember to include URL links to anywhere else where you have posted the same question. )
Providing us that infomation will help all of us in the future. :)

Thanks

Alan

*** Please use Google drive or similar for the .csv file.
It is important that we are given the actual .csv file and not an Excel File which we must convert to a .csv file

[fixer]

Sub STEP10()
Dim Wb1 As Workbook, Wb2 As Workbook, WB3 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet, WS3 As Worksheet
Dim WSM As Worksheet
Dim MaxData1 As Long, MaxCol3 As Long, I As Long
Dim FPath As String, sFile1 As String
Dim Rng As Range
Dim bCloseExit As Boolean


With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With


For I = 1 To 3

Select Case I
Case 1
On Error Resume Next
Set Wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktop1.xls")
If Err 0 Then

bCloseExit = True
Else
On Error GoTo 0
Set Ws1 = Wb1.ActiveSheet
sFile1 = Wb1.FullName
End If
Case 2
On Error Resume Next
Set Wb2 = Workbooks.Open("C:UsersWolfieeeStyleDesktopAlert..csv")
If Err 0 Then

bCloseExit = True
Else
On Error GoTo 0
Set Ws2 = Wb2.ActiveSheet
End If
Case 3
On Error Resume Next
Set WB3 = Workbooks.Open("C:UsersWolfieeeStyleDesktopFilesAlertCodes.xlsx")
If Err 0 Then

bCloseExit = True
Else
On Error GoTo 0
Set WS3 = WB3.Worksheets.Item(3)
End If
End Select

If bCloseExit Then
Wb1.Close savechanges:=False
Wb2.Close savechanges:=False
WB3.Close savechanges:=False
Exit Sub
End If
Next I

MaxData1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row - 1
MaxCol3 = WS3.Cells(1, WS3.Columns.Count).End(xlToLeft).Column
Set Rng = WS3.Range(WS3.Range("A1"), WS3.Cells(1, MaxCol3))
Rng.COPY Ws2.Range("A1")
Ws2.Range(Ws2.Range("A1"), Ws2.Cells(MaxData1, MaxCol3)).FillDown


Wb1.Close savechanges:=False
WB3.Close savechanges:=False
Wb2.SaveAs FileName:=Wb2.FullName, FileFormat:=xlCSV
Wb2.Close
Set Ws1 = Nothing
Set Ws2 = Nothing
Set WS3 = Nothing
Set Wb1 = Nothing
Set Wb2 = Nothing
Set WB3 = Nothing


With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With





End Sub


This was the Macro Doc Sir

[fixer]

https://drive.google.com/open?id=14CqzQ1aXzTrDMs3PnSeqQBGB4M0mGW95
https://drive.google.com/open?id=1bnRziKqm7kKTIaBieIW7wz8bfuj6YnOw
https://drive.google.com/open?id=16__giia6wZtiyy_qrCtRn_ns23nvKrX4


File Link Doc Sir

[DocAElstein]

I have no idea what that macro is supposed to be doing.
In any case it errors in three places
MacroError.JPG : : https://imgur.com/TT9h9fz
2931

The file names uploaded and the file names in the macro are inconsistent.

You appear to have hurriedly uploaded a few files and some coding.

Please read again : 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=13354&viewfull=1#post13354

[fixer]

in the path( / )is not mentioned,it was my mistake .
Actually this problem was solved, so i deleted the sample file,& since i deleted the sample file, i made a new one & shared with u Doc Sir

[fixer]

https://www.experts-exchange.com/questions/29181435/Facing-issue-with-saving-of-the-file.html
I got help from experts exchange for this problem Doc Sir

[DocAElstein]

My first answer here was almost perfect. 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=13185&viewfull=1#post13185
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13184&viewfull=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


This 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
2936

That is the final result that I want


Here is the new answer from me : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13346&viewfull=1#post13346

Only a very small change was required:

' 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 text file. It is not an Excel file.
For example, in Notepad, it looks like this:
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs
2936

2.csv is a text 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

Sometimes 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


Alan

[fixer]

Correct Doc Sir, Thats what i wanted to say u at that time,only minor changes is required, Thnx Alot for helping me in solving the same Doc Sir

[fixer]

The code given by u Doc Sir No Doubt its perfect, But it requires a little change
it requires a little change bcoz i changed something in the macro, I am providing all the details below

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("C:\Users\WolfieeeStyle\Desktop\1.xls") ' Workbooks("1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xlsx")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\Alert..csv") ' Workbooks("2.csv") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\document\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xl sx") ' 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(3)
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:=w2.FullName, FileFormat:=xlCSV
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True

End Sub

Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function




I used this code & i am getting perfect output
i changed this line plz see

w2.SaveAs Filename:=ThisWorkbook.Path & "\2.csv", FileFormat:=xlCSV


to
this


w2.SaveAs Filename:=w2.FullName, FileFormat:=xlCSV

and i am getting this popup which i have attached plz see (If i click on Yes then i am getting the desired output & i want that to be done by vba )

[DocAElstein]

Try

' 3b
Let Application.DisplayAlerts = False
w2.SaveAs Filename:=w2.FullName, FileFormat:=xlCSV
'Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True

End Sub

[fixer]

Thnx Alot Doc Sir For Ur Great Help
Have a Awesome Day

[DocAElstein]

Avinash


Remember to always please give a link to everywhere else that you post,
……Sorry for not giving a link bcoz i thought no one is working on this problem so Time will not be wasted of anyone
I will take care of the same in future...

Once again you have blatently lied to me and been disrespectful and ignored what we have said to you:
Cross Post:
https://chandoo.org/forum/threads/copy-and-paste.44182/
You are continuing to be disrespectful and annoying all people trying hard to help you.
This is becoming intolerable to everyone.

Alan

Edit Good Grief! Yet another one! https://www.ozgrid.com/forum/index.php?thread/1227413-copy-and-paste/&pageNo=2
So we have, at least
https://chandoo.org/forum/threads/copy-and-paste.44182/
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=13439&viewfull=1#post13439
https://www.experts-exchange.com/questions/29181435/Facing-issue-with-saving-of-the-file.html
https://www.ozgrid.com/forum/index.php?thread/1227413-copy-and-paste/&pageNo=2
http://www.eileenslounge.com/viewtopic.php?f=30&t=34508

[fixer]

Sorry for the same & this will not be repeated in future

[fixer]

I have not lied it to u
U told me from where u got the solution of that problem then I told u that from experts exchange, I got it & shared u the link also


If u see all the post then U will notice I posted the question but question was not solved by anyone of them except experts exchange & that macro also has some issue if i ran the macro with less data & then again I consider ur code & made some changes by you & problem solved on that day

[DocAElstein]

Avinash
You continually lie , cross post, and ignore what you are asked. If you seriously do not believe this fact yourself, then you should seek medical / psychological help quickly, as you are probably dangerously insane.



Sorry for the same & this will not be repeated in future
This you have already said many times here and elsewhere. Like many things you write, it often does not mean what it says… it is a lie.. or you are incapable of keeping your promise.. Or you believe it to be correct/ true, in which case you should seek medical / psychological help quickly, as you are probably dangerously insane.



It is very, very simple. So simple that even you can understand it:
Every time you post the same question anywhere, then immediately after tell everybody everywhere of all the cross posts

I have tried to tell you per PM the conditions for me to help you ever again.

If you insult me by
lying ever again,
or not reading anything that I have written,
or not responding as I want,
Then I will ban you immediately. ( And if I ever come to Mumbai in India, then I will find you, and I will beat the fucking shit out of you, Bro! )

Alan

[fixer]

%D
Relax now I understood what u mean to say about .csv
I am seeing error sometime's & now I understood the game

Actually I will let u know what happened
As u know I am present in most of the forum & I ask question in that forum & many coders have done so(they have opened text file by excel,I do not know the exact technical language to describe what they have done)& u r doing something different which was new for me & I got confused

Let the story be xyz, Now I understood the Game & I will not try to play with .csv & I will take ur suggestion before doing anything with .csv files

Today I saw an error while I was uploading the .CSV file to the system
There were 164 stocks but system shown only 3 stocks
& Again I cancelled that & Again I reuploaded the file to system then it showed 164 & system showed the problem as comparator issue for the error

No problem

Till today I was ignoring ur .csv explanation only bcoz I have not got any errors till today but now I got it ,So working on the same

Thnx for ur Great help & Thnx Alot for ur Great Guidance in making the things perfect & Accurate

[DocAElstein]

.... system showed the problem as comparator issue for the error....
So very slowly you may understand something…

Just for your info
Seperated = Delimeter = Comparator

Comma Seperated Values file
=
Comma delimeted Values files
=
Comma comparator files
=
comma delimited files
=
Comma comparator seperated files

etc…

[DocAElstein]

When you give an explanation on a file… Do not do it on a .csv file

Better is to give 2 text files , a before and after
Before.csv
After.csv

Before.csv is the file to use in a macro
After.csv is the file that the macro should give you



For forum question:

_1) Write Explanation or question in forum post

_2) Explanation can also be in Excel file

_3) Do not give an explanation in a text ( .csv or .txt ) file

_4) Before and After
_4a) For Excel files you can give Before and After.
It can be two worksheets, SheetBefore and SheetAfter in one Excel File,
or
it can be two Files, like Before.xls and After.xls

_4b) For text files you can give Before and After.
But it must be 2 files ( text files do not have worksheets ), like Before.csv and After.csv

[fixer]

Sure Doc Sir
From Now there will be no.csv files in my Question%O

[DocAElstein]

....From Now there will be no.csv files in my Question%O
This is your choice. But it may not be a good decision…

There are sometimes problems with Excel VBA and Excel Files. There are sometimes not problems with Excel VBA and Excel Files
There are sometimes problems with .csv Files. There are sometimes not problems with .csv Files
No difference. Sometime problems. Sometimes no problems with either. Maybe sometimes different problems.

Excel is good for if you want lots of formats ( like colours ) in worksheets.
Text files are sometimes better for just values.

For Excel Files
.xlsm and .xlsx are sometimes slow and inefficient for lots of values
.xls is sometime a little better

For Text files
.csv and .txt are very similar. Sometimes they are almost the same. Sometimes they are exactly the same.
For lots of just values, ( for no formats like no color ) , text files are better than any Excel files

If you have a system that handles lots of values only, with no cell or spreadsheet formats, then usually they will use text files


Excel VBA is good for controlling Excel Files
Excel VBA is good for controlling Text files

It is just sometimes bad to try to Open a text file into Excel.
To open an Excel file with Excel VBA is no problems usually.
To import data from a text file into Excel is also usually no problem.


This is mostly a problem…
To try
Workbooks.Open ____________.csv
or
Workbooks.Open ____________.txt
These two things are often big problem

[fixer]

No problem
Now there will not be any problem
Bcoz No .CSV files is used
I have many codes that are doing great Job
But only the macro of .CSV file caused issue but from now I will not used .CSV instead of .CSV I will upload .text file in my system & it will become Perfect