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

[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/showthrea...ll=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/showthrea...ll=1#post13193 https://drive.google.com/file/d/1MFI...LjkblGxfo/view https://drive.google.com/file/d/1V_8...Sd5zlDcVX/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/showthrea...ll=1#post13217
After you run that macro , you will see two new files:
New made Dog Shit files.JPG : https://imgur.com/UUZ09z6
New made Dog Shit files.jpg

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_8...Sd5zlDcVX/view )
After csv.JPG


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
---
I did it with M-code but I really don't know what fixer want as the result

// 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

[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