vba macro Modification
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
vba macro Modification
I have mentioned all the details in the file and i have attached the macro also in the file,
So plz see the same and help me out in solving this problem Sir
this macro has errors sometimes it works perfectly and sometimes it works incorrectly
Instead of pasting the data to column B sometimes it paste to column A
I was unable to upload the file so i shared this link to download the file
https://drive.google.com/open?id=1f282q ... QmiN2py1bM
https://drive.google.com/open?id=1H-wIz ... ajOgliDD2A
https://drive.google.com/open?id=1HL_U2 ... Yqutshv3_Q
https://drive.google.com/open?id=1tyjYl ... qC5PkHCeub
So plz see the same and help me out in solving this problem Sir
this macro has errors sometimes it works perfectly and sometimes it works incorrectly
Instead of pasting the data to column B sometimes it paste to column A
I was unable to upload the file so i shared this link to download the file
https://drive.google.com/open?id=1f282q ... QmiN2py1bM
https://drive.google.com/open?id=1H-wIz ... ajOgliDD2A
https://drive.google.com/open?id=1HL_U2 ... Yqutshv3_Q
https://drive.google.com/open?id=1tyjYl ... qC5PkHCeub
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba macro Modification
As has been pointed to you before, here and elsewhere, a .csv file is not an Excel workbook, but a plain text file. You can open a .csv file in Excel, and Excel will do its best to display the data in columns.
But if you want consistent results, use a real Excel workbook (a .xlsx file) instead of a .csv file.
But if you want consistent results, use a real Excel workbook (a .xlsx file) instead of a .csv file.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
I have the macro for that problem who consistently give correct output in csv file
that was the different problem(question) and this is different problem(question)
I tried to make a macro and i created the macro
I request u to plz download the file and see the macro and correct it (bcoz u r a professional coder)
My previous question Solution Macro is pasted below
that was the different problem(question) and this is different problem(question)
I tried to make a macro and i created the macro
I request u to plz download the file and see the macro and correct it (bcoz u r a professional coder)
My previous question Solution Macro is pasted below
Code: Select all
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba macro Modification
I downloaded the files you linked to above. The macro appears to be the one we discussed before...
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
Ok HansV, i forgot that i have discussed the same with u, Sorry for the same & i can't convert that csv to xlsx or xls or any,
But Being a Professional vba coder u dont have any solution for the same?
But Being a Professional vba coder u dont have any solution for the same?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba macro Modification
The code works OK for me; I don't know why it sometimes doesn't work for you.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
No problem i am sharing the actual file plz see the same and run the macro and see how it is pasting the data to column A instead of column B HansV Sir
https://drive.google.com/open?id=1CGJVt ... 5REXRDG-r3
https://drive.google.com/open?id=1SgQVr ... iBc_k2RrIF
https://drive.google.com/open?id=1AVX7g ... O_lInzU36y
https://drive.google.com/open?id=1CGJVt ... 5REXRDG-r3
https://drive.google.com/open?id=1SgQVr ... iBc_k2RrIF
https://drive.google.com/open?id=1AVX7g ... O_lInzU36y
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba macro Modification
Does this work better for you?
Code: Select all
Sub Macro()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim m As Long
Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
Set Wb2 = Workbooks.Add(Template:=xlWBATWorksheet)
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(1)
With Ws1
m = .Range("I" & .Rows.Count).End(xlUp).Row
.Range("I2:I" & m).Copy Ws2.Range("B1")
End With
Ws2.Range("A1:A" & m - 1).Value = Chr(160)
Application.DisplayAlerts = False
Wb2.SaveAs Filename:="C:\Users\**I've been banned**\Desktop\Alert..csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
Wb2.Close SaveChanges:=False
Wb1.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
But if there is data in csv file then this code delete's that & I don't want that to happen
plz see the sample file and run the Macro HansV Sir (I know previously i sent u blank csv file but if that is not blank then this macro will lead to trouble)
https://drive.google.com/open?id=17c3Ss ... LZGDF-j0xT
https://drive.google.com/open?id=1tGJpA ... dDoaY1NhwX
plz see the sample file and run the Macro HansV Sir (I know previously i sent u blank csv file but if that is not blank then this macro will lead to trouble)
https://drive.google.com/open?id=17c3Ss ... LZGDF-j0xT
https://drive.google.com/open?id=1tGJpA ... dDoaY1NhwX
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba macro Modification
If there are already data in the first column of the csv file when you open it in Excel, the original macro should work fine.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
Code: Select all
Sub Macro()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim m As Long
Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktop1.xls")
Set Wb2 = Workbooks.Open("C:UsersWolfieeeStyleDesktopAlert..csv")
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(1)
With Ws1
m = .Range("I" & .Rows.Count).End(xlUp).Row
.Range("I2:I" & m).Copy Ws2.Range("B1")
End With
Ws2.Range("A1:A" & m - 1).Value = Chr(160)
Wb2.Close savechanges:=True
Wb1.Close savechanges:=False
Application.ScreenUpdating = True
End Sub
No HansV Sir i tried but it was not giving proper result (it is pasting the data in column B but it is delteing the data in column A of csv)
i pasted the macro, plz run the macro and see the output it is deleting column A data
files
https://drive.google.com/open?id=1tGJpA ... dDoaY1NhwX
https://drive.google.com/open?id=17c3Ss ... LZGDF-j0xT
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba macro Modification
Remove the line
Code: Select all
Ws2.Range("A1:A" & m - 1).Value = Chr(160)
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
Awesome ! HansV Sir Thnx Alot For ur Great Guidance
Problem Solved
Problem Solved
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: vba macro Modification
@ Hans
Hi Hans,
This is just some follow up notes here, that may be helpful for future reference to both of us…
( BTW. These CR1 , CR2 , CR3 are just examples of “Canned” replies, which the OP probably has never translated and has no idea what they are saying. Occasionally they mean what they say, but often they mean ..”Oh **** , I am totally confused again, and so is everyone else, so lets get out of here quick, forget it , and re post the same here or somewhere else again now or later” ….)
_.____
Your statement at the start of this Thread, ( which is as lost on the OP as anything and everything ether of us has ever said to him ) , goes some way to explain the issues… “
__ …. You can open a .csv file in Excel, and Excel will do its best to display the data in columns….”
My Excel , your Excel , and the OP’s Excel may not always get the same result in its attempt to do this. (In addition how Excel handles the files will vary with how you try to do the opening).
_.______
__ Furthermore, the following is , I think, useful for us both , for future reference, ( and for anyone else brave enough to attempt to help the OP on any issues involving using his so called “.csv files” ) ……
In the course of the last few months, very similar, sometimes identical, questions have been posted by the OP, ( Avinash) , in more forums and versions than I can remember, which contain a file with the name of either
2.csv
or
Alert..csv
After re reading and examining the files, and various Threads, I have come to the following conclusions:
Those files have , seemingly randomly, had any of the following formats.
CommaSeperatedValues.csv
SemiColonSeperatedValues.csv
TabSeperatedValues.csv
ExcelFileWithWrongExtension.csv
Those example files above are three text files, and the forth is an Excel file with the wrong extension. Any simple macro such as the following will open and close those files and preserve the format. ( I mean by "preserve the format" that the file type will not be changed, - Of course for the first three text files, any cell formating will be lost by closing )
__ To complicate matters worse,
_ the same named file can appear in the same Thread at different times in any of those 4 different formats. ( Note: sometimes the uploaded file is removed, and re uploaded in a different format! Sometimes the files are removed/trashed )
_ it is my further conclusion that any so called “.csv files” , used to test any macro given by us, will randomly have any of those formats. The testing is usually done once very quickly. Sometimes shortly after this "final" testing and issue of the canned reply, all data files and macro are deleted along with any infomation in memory, human or otherwise.
Whether or not our given macros actually do what is finally wanted, is anyone guess. Usually they don’t: The OPs ( sometimes “Canned” ) final replies rarely tell us anything. In any case we can expect to be going around in circles indefinitely attempting to answer the same question, or very similar ones, involving his so called “.csv files”
This is due to the OPs inability or unwillingness to understand anything about the difference between Excel and Text files.
__ One final extra complicating issue. Frequently in the course of a Thread, the OP uploads files or posts code ( either deliberately or by mistake ) which have nothing what so ever to do with the issues at hand!!!
_.___
A solution that might come close to solving some of the “.csv files” containing questions from this OP, would need to
_1 have a section to determine which of those 4 file formats is currently being used
( _ 2 the section that includes doing what is wanted to be done to that file) ,
_ 3 The final section will need to be one which offers all of those 4 formats, and in addition shows how they open in various Excel versions, and finally asks the OP to select the one he wants)
There is still no guarantee that this will give the OP what he wants, as that file will be used somewhere else, ( and possibly, by accident , in a different format again ( I mean different file type/ extension but with same basic values content) , … and the whole confusion and duplicate re posting will start again.
Alan
_.___
Ref ( Here are just a few from the many mixed up crossed and duplicated postings )
http://www.eileenslounge.com/viewtopic. ... 05#p268605
https://excelfox.com/forum/showthread.p ... #post13355
https://www.experts-exchange.com/questi ... -file.html
http://www.eileenslounge.com/viewtopic.php?f=30&t=34497
https://excelfox.com/forum/showthread.p ... #post13208
https://excelfox.com/forum/showthread.p ... #post13369
Hi Hans,
This is just some follow up notes here, that may be helpful for future reference to both of us…
( BTW. These CR1 , CR2 , CR3 are just examples of “Canned” replies, which the OP probably has never translated and has no idea what they are saying. Occasionally they mean what they say, but often they mean ..”Oh **** , I am totally confused again, and so is everyone else, so lets get out of here quick, forget it , and re post the same here or somewhere else again now or later” ….)
_.____
Your statement at the start of this Thread, ( which is as lost on the OP as anything and everything ether of us has ever said to him ) , goes some way to explain the issues… “
__ …. You can open a .csv file in Excel, and Excel will do its best to display the data in columns….”
My Excel , your Excel , and the OP’s Excel may not always get the same result in its attempt to do this. (In addition how Excel handles the files will vary with how you try to do the opening).
_.______
__ Furthermore, the following is , I think, useful for us both , for future reference, ( and for anyone else brave enough to attempt to help the OP on any issues involving using his so called “.csv files” ) ……
In the course of the last few months, very similar, sometimes identical, questions have been posted by the OP, ( Avinash) , in more forums and versions than I can remember, which contain a file with the name of either
2.csv
or
Alert..csv
After re reading and examining the files, and various Threads, I have come to the following conclusions:
Those files have , seemingly randomly, had any of the following formats.
CommaSeperatedValues.csv
SemiColonSeperatedValues.csv
TabSeperatedValues.csv
ExcelFileWithWrongExtension.csv
Those example files above are three text files, and the forth is an Excel file with the wrong extension. Any simple macro such as the following will open and close those files and preserve the format. ( I mean by "preserve the format" that the file type will not be changed, - Of course for the first three text files, any cell formating will be lost by closing )
Code: Select all
Sub Open_SoCalled_csvfiles() ' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13247&viewfull=1#post13247 https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13208&viewfull=1#post13208 http://www.eileenslounge.com/viewtopic.php?p=268627#p268627
' change this path name to suit
Dim Paf As String: Let Paf = ThisWorkbook.Path & "\csv file Chaos\"
Workbooks.Open Filename:=Paf & "CommaSeperatedValues.csv" ' Share ‘CommaSeperatedValues.csv’ : https://app.box.com/s/w2barpwasveltam4lutjwijks0zft0vq
ActiveWorkbook.Close
Workbooks.Open Filename:=Paf & "SemiColonSeperatedValues.csv" ' Share ‘SemiColonSeperatedValues.csv’ : https://app.box.com/s/kvqqfsjaebzj684rw8n0u1v4hqfi3hea
ActiveWorkbook.Close
Workbooks.Open Filename:=Paf & "TabSeperatedValues.csv" ' Share ‘TabSeperatedValues.csv’ : https://app.box.com/s/ukgxcmxj8xhmy0gzvw5269zyjdmun28g
ActiveWorkbook.Close
Workbooks.Open Filename:=Paf & "ExcelFileWithWrongExtension.csv" ' Share ‘ExcelFileWithWrongExtension.csv’ : https://app.box.com/s/esxlg0ovoux4gk29zxgklwog6zz6b7s1
ActiveWorkbook.Close
End Sub
_ the same named file can appear in the same Thread at different times in any of those 4 different formats. ( Note: sometimes the uploaded file is removed, and re uploaded in a different format! Sometimes the files are removed/trashed )
_ it is my further conclusion that any so called “.csv files” , used to test any macro given by us, will randomly have any of those formats. The testing is usually done once very quickly. Sometimes shortly after this "final" testing and issue of the canned reply, all data files and macro are deleted along with any infomation in memory, human or otherwise.
Whether or not our given macros actually do what is finally wanted, is anyone guess. Usually they don’t: The OPs ( sometimes “Canned” ) final replies rarely tell us anything. In any case we can expect to be going around in circles indefinitely attempting to answer the same question, or very similar ones, involving his so called “.csv files”
This is due to the OPs inability or unwillingness to understand anything about the difference between Excel and Text files.
__ One final extra complicating issue. Frequently in the course of a Thread, the OP uploads files or posts code ( either deliberately or by mistake ) which have nothing what so ever to do with the issues at hand!!!
_.___
A solution that might come close to solving some of the “.csv files” containing questions from this OP, would need to
_1 have a section to determine which of those 4 file formats is currently being used
( _ 2 the section that includes doing what is wanted to be done to that file) ,
_ 3 The final section will need to be one which offers all of those 4 formats, and in addition shows how they open in various Excel versions, and finally asks the OP to select the one he wants)
There is still no guarantee that this will give the OP what he wants, as that file will be used somewhere else, ( and possibly, by accident , in a different format again ( I mean different file type/ extension but with same basic values content) , … and the whole confusion and duplicate re posting will start again.
Alan
_.___
Ref ( Here are just a few from the many mixed up crossed and duplicated postings )
http://www.eileenslounge.com/viewtopic. ... 05#p268605
https://excelfox.com/forum/showthread.p ... #post13355
https://www.experts-exchange.com/questi ... -file.html
http://www.eileenslounge.com/viewtopic.php?f=30&t=34497
https://excelfox.com/forum/showthread.p ... #post13208
https://excelfox.com/forum/showthread.p ... #post13369
Last edited by Doc.AElstein on 18 May 2020, 17:11, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 4StarLounger
- Posts: 585
- Joined: 14 Nov 2012, 16:06
Re: vba macro Modification
@Doc,
You should also indicate which country the bloke is originating from.
You should also indicate which country the bloke is originating from.
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: vba macro Modification
I am getting perfect things what i wanted from all of u, a great help from all of u
All the codes which has provided by everyone gives correct output
Thnx Alot Doc Sir & HansV Sir & snb Sir
All the codes which has provided by everyone gives correct output
Thnx Alot Doc Sir & HansV Sir & snb Sir