vba macro Modification

[zyxw1234]

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

[HansV]

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.

[zyxw1234]

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

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

[HansV]

I downloaded the files you linked to above. The macro appears to be the one we discussed before...

[zyxw1234]

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?

[HansV]

The code works OK for me; I don't know why it sometimes doesn't work for you.

[zyxw1234]

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

[HansV]

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

[zyxw1234]

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

[HansV]

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.

[zyxw1234]

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

[HansV]

Remove the line

Code: Select all

    Ws2.Range("A1:A" & m - 1).Value = Chr(160)

[zyxw1234]

Awesome ! HansV Sir Thnx Alot For ur Great Guidance
Problem Solved

[Doc.AElstein]

@ 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 )

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

__ 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

[snb]

@Doc,

You should also indicate which country the bloke is originating from.

[zyxw1234]

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