Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Later
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Later
A Folk, A Forum, A Fuhrer ….
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
VBA To Copy Rows From One Workbook To text csv File, Based On Count In A Different Workbook
Question
I have three files 2 Excel Files,1.xls & 3.xlsx , and a text file, 2.csv
1.xls first row has headers so don't count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that many rows of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything
The final result should be a comma separated values text file , 2.csv.
For example, in Notepad, it looks like this:
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs
Attachment 2935
That is the final result that I want
Answer:
Code:Sub Step14() ' https://excelfox.com/forum/showthread.php/2467-VBA-To-Copy-Rows-From-One-Workbook-To-text-csv-File-Based-On-Count-In-A-Different-Workbook?p=13367&viewfull=1#post13367 ' http://www.eileenslounge.com/viewtopic.php?f=30&t=34508 (zyxw123) https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13182#post13182 Rem 1 Worksheets info Dim w1 As Workbook, w2 As Workbook, w3 As Workbook Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls") ' Workbooks("1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xlsx") Set w2 = Workbooks.Open(ThisWorkbook.Path & "\2.csv") ' Workbooks("2.csv") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\document\2.csv") Set w3 = Workbooks.Open(ThisWorkbook.Path & "\3.xlsx") ' Workbooks("3.xlsx") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\files\3.xlsx") Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet Set WS1 = w1.Worksheets.Item(1) Set WS2 = w2.Worksheets.Item(1) Set WS3 = w3.Worksheets.Item(1) Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long Let Lr1 = WS1.Range("A" & WS1.Rows.Count & "").End(xlUp).Row ' http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) Let Lc3 = WS3.Cells.Item(1, WS3.Columns.Count).End(xlToLeft).Column Dim Lc3Ltr As String Let Lc3Ltr = CL(Lc3) Rem 2 ' In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv Let Lenf1 = Lr1 - 1 ' 1.xls first row has headers so dont count that ' 2a) Dim rngOut As Range: Set rngOut = WS2.Range("A1:" & Lc3Ltr & Lenf1 & "") '' 2b)(i) Relative formula referrences ... https://teylyn.com/2017/03/21/dollarsigns/#comment-191 ' WS2.Cells.NumberFormat = "General" ' May be needed to prevent formulas coming out as test =[3.xlsx]Sheet1!$A$1 ' Let rngOut.Value = "='[3.xlsx]" & WS3.Name & "'!A$1" ' Let rngOut.Value = rngOut.Value ' Change Formulas to values ' Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))") ' https://excelribbon.tips.net/T010741_Removing_Spaces ' Or ' 2b)(ii) Copy Paste Dim rngIn As Range Set rngIn = WS3.Range("A1:" & Lc3Ltr & "1") rngIn.Copy rngOut.PasteSpecial Paste:=xlPasteValues ' understanding Paste across ranges of different size to Copy range : https://excelfox.com/forum/showthread.php/2221-VBA-Range-Insert-Method-Code-line-makes-a-space-to-put-new-range-in?p=10441&viewfull=1#post10441 Rem 3 ' 3a w1.Close w3.Close ' 3b w2.SaveAs Filename:=ThisWorkbook.Path & "\2.csv", FileFormat:=xlCSV Let Application.DisplayAlerts = False w2.Close Let Application.DisplayAlerts = True End Sub
A Folk, A Forum, A Fuhrer ….
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Later
My first answer here was almost perfect. https://excelfox.com/forum/showthrea...ll=1#post13185
https://excelfox.com/forum/showthrea...ll=1#post13184
This was your question:
i have three files 1.xls & 2.csv & 3.xlsx
1.xls first row has headers so dont count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything
You question should have been you question:
VBA To Copy Rows From One Workbook To text csv File, Based On Count In A Different Workbook
I have three files 2 Excel Files,1.xls & 3.xlsx , and a text file, 2.csv
1.xls first row has headers so don’t count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that many rows of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything
The final result should be a comma separated values text file , 2.csv.
For example, in Notepad, it looks like this:
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs
That is the final result that I want
Here is the new solution from me : https://excelfox.com/forum/showthrea...ll=1#post13346
Only a very small change was required:
Code:' 3b w2.SaveAs Filename:=ThisWorkbook.Path & "\2.csv", FileFormat:=xlCSV Let Application.DisplayAlerts = True w2.Close
Avinash
Read this, and try to understand at least a little of it.
2.csv is a test file. It is not an Excel file.
For example, in Notepad, it looks like this: [/color]
2csv is a comma seperated text file.JPG : https://imgur.com/FEjKVMs
2.csv is a test file. It is not an Excel file.
You can open a .csv file in Excel, and Excel will do its best to display the data in columns
Sometime Excel will do this:
_____ Workbook: 2.csv ( Using Excel 2007 32 bit )
Worksheet: 2
Row\Col NSE A GTT NSE A GTT NSE A GTT NSE A GTT NSE A GTT
Sometimes Excel will do this:
_____ Workbook: 2.csv ( Using Excel 2007 32 bit )
Worksheet: 2
Row\Col NSE,,6,,,A,,,,,GTT NSE,,6,,,A,,,,,GTT NSE,,6,,,A,,,,,GTT NSE,,6,,,A,,,,,GTT NSE,,6,,,A,,,,,GTT
A Folk, A Forum, A Fuhrer ….
https://excelfox.com/forum/showthrea...sx-to-txt-file
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Alert..txt from Avinash : FromAvinashTextFileAlet__txt.JPG : https://imgur.com/HDHgB0z
Code:USA,101010,6,<,12783,A,,,,,GTT, USA,22,6,<,12783,A,,,,,GTT, USA,17388,6,<,12783,A,,,,,GTT, USA,100,6,<,12783,A,,,,,GTT, USA,25,6,<,12783,A,,,,,GTT,Code:"USA" & "," & "101010" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "22" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "17388" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "100" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "25" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLfYou will see that vbLf is the separator for lines(records)Code:"USA" & "," & "101010" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "22" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "17388" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "100" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf & "USA" & "," & "25" & "," & "6" & "," & Chr(60) & "," & "12783" & "," & "A" & "," & "," & "," & "," & "," & "GTT" & "," & vbLf
This is the macro i used to get that infomation:
Code:Sub WhatStringIsInAlertDotDot_txt() ' 9th June 2020 https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\Alert..txt" ' This would be made if not existing and we would have a zero lenf string Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input... Dim Lenf As Long: Let Lenf = LOF(FileNum) TotalFile = Space(Lenf) '....and wot recives it hs to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum ' What the fuck is in this string? Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' 'https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page34#post13699 https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11015&viewfull=1#post11015 End Sub
Here is the macro to answer this thread
https://excelfox.com/forum/showthrea...sx-to-txt-file
Code:' https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file Sub xlsxTotxt_LineSeperatorvbLf_valuesSeperatorComma() Rem 1 Workbooks info Dim Wb1 As Workbook: Set Wb1 = Workbooks("sample2.xlsx") Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1) Dim Lr As Long, Lc As Long Let Lr = Ws1.Cells.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row Let Lc = Ws1.Cells.Item(1, Ws1.Columns.Count).End(xlToLeft).Column Dim arrIn() As Variant: Let arrIn() = Ws1.Range(Ws1.Range("A1"), Ws1.Cells.Item(Lr, Lc)).Value ' Data range in sample2.xlsx Rem 2 make text file long string Dim Rw As Long, Clm As Long ' For Rw = 1 To Lr ' each row in Ws1 For Clm = 1 To Lc ' each column for each row in Ws1 Dim strTotalFile As String Let strTotalFile = strTotalFile & arrIn(Rw, Clm) & "," ' add a value and a seperator for this line Next Clm Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1) ' this will take off the last , Let strTotalFile = strTotalFile & vbLf ' this adds the line seperator wanted by Avinash - https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page30#post13348 - You will see that vbLf is the separator for lines(records) Next Rw Let strTotalFile = Left(strTotalFile, Len(strTotalFile) - 1) ' this takes off the last vbLf Debug.Print strTotalFile Rem 3 make text file from the total string Dim FileNum As Long Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open ThisWorkbook.Path & "\csv Text file Chaos\Alert..txt" For Output As #FileNum ' CHANGE TO SUIT ' Will be made if not there Print #FileNum, strTotalFile ' strTotalFile Close #FileNum End Sub
A Folk, A Forum, A Fuhrer ….
( This post is https://excelfox.com/forum/showthrea...ge30#post13349 )
Some notes related to these posts
https://excelfox.com/forum/showthrea...rt-Csv-To-Xlsx https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364
See here ( This post https://excelfox.com/forum/showthrea...ge30#post13349 )
for typical comparisons of text Files, Excel files, and data files
Text File: https://excelfox.com/forum/showthrea...ll=1#post13693
Excel File: https://excelfox.com/forum/showthrea...ll=1#post13694
Data File: https://excelfox.com/forum/showthrea...ll=1#post13695
Excel File
_____ Workbook: Alert..xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
A Folk, A Forum, A Fuhrer ….
( This post is https://excelfox.com/forum/showthrea...ll=1#post13693 )
Some notes related to these posts
https://excelfox.com/forum/showthrea...rt-Csv-To-Xlsx https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364
Note: With Text files we must concern ourselves with the Record/Line(row) separator and the Field(column) Separator: They may vary. We must know about these.
A Folk, A Forum, A Fuhrer ….
( This post is https://excelfox.com/forum/showthrea...ll=1#post13694 )
Some notes related to these posts
https://excelfox.com/forum/showthrea...rt-Csv-To-Xlsx https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364
In Excel we do not have to concern ourselves with the row separator used internally by Excel ( vbCr & vbLf ), or the column Separator used internally by Excel ( vbTab ) : Excel does this for us. We do not need to add these when working with Excel Files. Internally, Excel uses those separators to make the cells that we see and work with.
_____ Workbook: Alert..xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Excel Files A B C D E F G H I J K 1 USA vbTab 101010 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT vbCr & vbLf 2 USA vbTab 22 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT vbCr & vbLf 3 USA vbTab 17388 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT vbCr & vbLf 4 USA vbTab 100 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT vbCr & vbLf 5 USA vbTab 25 vbTab 6 vbTab < vbTab 12783 vbTab A vbTab vbTab vbTab vbTab vbTab GTT vbCr & vbLf
Note: In Excel we do not have to concern ourselves with the row seperator, vbCr & vbLf or the column Seperator, vbTab: Excel does this for us. We do not need to add these when working with Excel Files
We will only see this:
_____ Workbook: Alert..xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Excel Files A B C D E F G H I J K L 1 USA 101010 6 < 12783 A GTT 2 USA 22 6 < 12783 A GTT 3 USA 17388 6 < 12783 A GTT 4 USA 100 6 < 12783 A GTT 5 USA 25 6 < 12783 A GTT 6
A Folk, A Forum, A Fuhrer ….
( This post is https://excelfox.com/forum/showthrea...ll=1#post13695 )
Some notes related to these posts
https://excelfox.com/forum/showthrea...rt-Csv-To-Xlsx https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
http://www.eileenslounge.com/viewtop...269104#p269104
http://www.eileenslounge.com/viewtopic.php?f=30&t=34638
https://chandoo.org/forum/threads/fe...2/#post-264364
Data files are held in computer memory in different forms and retrieved in different ways. Any particular value may be referrenced in many different ways.
A Folk, A Forum, A Fuhrer ….
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Later
A Folk, A Forum, A Fuhrer ….
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Later
A Folk, A Forum, A Fuhrer ….
Posting Permissions
Reply With Quote
Bookmarks