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

[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/showthrea...ll=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/showthrea...ll=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/showthrea...ll=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 )
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/showthrea...ll=1#post13150
https://excelfox.com/forum/showthrea...ll=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/showthrea...ll=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/showthrea...ll=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
1	zyxw123	jhas		rider	roger	anjus	sumanjjj
2	Leonardo	umpsbug	kinjals		tinamishra	kinjal124	WigWam
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/showthrea...ll=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/showthrea...ll=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/showthrea...ll=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....l

Me 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/showthrea...ll=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
Excel Files.JPG

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
Text Files.JPG

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/showthrea...ll=1#post13246
ThisCanNeverBeTheResult.JPG

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/showthrea...ll=1#post13343 )