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

Printable View

Show 40 post(s) from this thread on one page

05-03-2020, 05:27 PM
fixer

3 Attachment(s)

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

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/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=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=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
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=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

Code:

' 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

Code:

' 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

1
NSE
6
A GTT

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

1
Exchange Symbol Series/Expiry Open High Low Prev Close LTP

2
NSE ACC EQ
1182

1193

1151.7

1190.45

1156.6

22

11.566

116815

1168.166

3
NSE ADANIENT EQ
137.15

140.55

134.1

140.5

134.65

25

1.3465

13595

135.9965

4
NSE ADANIPORTS EQ
273.95

276.95

269.55

277.6

270.65

15083

2.7065

27335

273.3565

5
NSE ADANIPOWER EQ
32.3

32.35

30.45

32.45

30.65

17388

0.3065

3095

30.9565

6
NSE AMARRAJA EQ
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/showthrea...ll=1#post13184

_____ 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

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

05-03-2020, 10:35 PM
fixer

1 Attachment(s)

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

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

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/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
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=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
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=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.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.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
05-03-2020, 11:28 PM
DocAElstein

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

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 End Sub

What does 2.csv look like after running above macro
05-03-2020, 11:41 PM
fixer

1 Attachment(s)

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=320960#p320960
https://eileenslounge.com/viewtopic.php?p=320957#p3209573
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
05-04-2020, 02:20 AM
DocAElstein

This may be a complicated issue. ( I also said this here: ( https://excelfox.com/forum/showthrea...ll=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/viewtop...=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_...le_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
05-04-2020, 03:17 AM
fixer

2 Attachment(s)

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
05-04-2020, 01:56 PM
DocAElstein

Once again …
This may be a complicated issue. ( I also said this here: ( https://excelfox.com/forum/showthrea...ll=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.

Quote:

Originally Posted by fixer

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_...le_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
05-04-2020, 02:35 PM
fixer

Before csv file link https://drive.google.com/open?id=1MF...s6EWCLjkblGxfo
after runing macro csv link https://drive.google.com/open?id=1V_...S63idSd5zlDcVX
05-04-2020, 05:54 PM
sandy666

there is chr(9) not chr(160)

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1182	1193	1151.7	1190.45	1156.6	22	11.566	116815	1168.166
3	NSE	ADANIENT	EQ	137.15	140.55	134.1	140.5	134.65	25	1.3465	13595	135.9965
4	NSE	ADANIPORTS	EQ	273.95	276.95	269.55	277.6	270.65	15083	2.7065	27335	273.3565
5	NSE	ADANIPOWER	EQ	32.3	32.35	30.45	32.45	30.65	17388	0.3065	3095	30.9565
6	NSE	AMARRAJA	EQ	555	555	529.25	557.85	532.1	100	5.321	5374	537.21
7

Show 40 post(s) from this thread on one page