Problem Solved Doc Sir & Sandy Sir
Thnx Alot for ur Great Support
I got the macro that does the same
Printable View
Problem Solved Doc Sir & Sandy Sir
Thnx Alot for ur Great Support
I got the macro that does the same
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
This was the Macro Doc SirCode: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
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
Attachment 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/showthrea...ll=1#post13354
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
https://www.experts-exchange.com/que...-the-file.html
I got help from experts exchange for this problem Doc Sir
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
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
Attachment 2936
That is the final result that I want
Here is the new answer 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 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
Attachment 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 )
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
Alan
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
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
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("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.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(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
Code:w2.SaveAs Filename:=ThisWorkbook.Path & "\2.csv", FileFormat:=xlCSV
to
this
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 )Code:w2.SaveAs Filename:=w2.FullName, FileFormat:=xlCSV
Try
Code:' 3b
Let Application.DisplayAlerts = False
w2.SaveAs Filename:=w2.FullName, FileFormat:=xlCSV
'Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
End Sub
Thnx Alot Doc Sir For Ur Great Help
Have a Awesome Day
Avinash
Quote:
Originally Posted by DocAElstein
Once again you have blatently lied to me and been disrespectful and ignored what we have said to you:Quote:
Originally Posted by fixer
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.p...aste/&pageNo=2
So we have, at least
https://chandoo.org/forum/threads/copy-and-paste.44182/
https://excelfox.com/forum/showthrea...ll=1#post13439
https://www.experts-exchange.com/que...-the-file.html
https://www.ozgrid.com/forum/index.p...aste/&pageNo=2
http://www.eileenslounge.com/viewtopic.php?f=30&t=34508
Sorry for the same & this will not be repeated in future
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
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.
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.Quote:
Originally Posted by fixer
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
%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
So very slowly you may understand something…Quote:
Originally Posted by fixer
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…
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
Sure Doc Sir
From Now there will be no.csv files in my Question%O
This is your choice. But it may not be a good decision…Quote:
Originally Posted by fixer
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
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