vba to convert a csv file to xlsx but some modification required

[RAM72]

Sorry but I didn't understand the « columns C and D row changes
the ranges headers » …
As my code works with your last csv joined files …
​

Marc

See attached screenshot of original csv and after saving again excel in csv the difference in column c and d . row 12 and row 14, row 1 and row3 for each csv.

Range headers differ also.

in doing so it makes both macros work as per 1st csv file post.

making the macros to work in original interface has issued macros errors.
So I think it is best saved in csv excel format and to apply the macros.

 

[Marc L]

Better is to join original csv file here as explained in post #2 …
​

 

[RAM72]

Better is to join original csv file here as explained in post #2 …
​

See attached exact replicate but in xlsx format, you can rename it to .csv.

I have a try but d'ont opened 108985csv.txt directly just rename it to 108985.csv and opened it

 

[Marc L]

Your .txt attachment is not a real text file, try !
So original file is not a text file or you did it wrong ?

For a true text file .csv, just rename it as .csv.txt and join it …
​

 

[Marc L]

As I can't answer to your conversation 'cause you locked it,
first try this mod based upon your screenshot (so untested) :

Sub Macro1_1()
      Application.DisplayAlerts = False
      Application.ScreenUpdating = False
      CSV$ = Dir(ThisWorkbook.Path & "\*.csv")
While CSV > ""
    N& = N& + 1
    Workbooks.OpenText ThisWorkbook.Path & "\" & CSV, xlWindows, , xlDelimited, xlTextQualifierNone, Comma:=True, DecimalSeparator:="."
  With ActiveSheet.UsedRange.Columns
         .Range("D1:D" & .Cells(1).End(xlDown).Row - 1).Clear
    With .Resize(, .Count + 1)
         .Item(.Count).Formula = "=D1="""""
         .Sort .Cells(.Count), xlAscending, Header:=xlNo
          Union(.Item(.Count), .Rows(Application.Match(True, .Item(.Count), 0) & ":" & .Rows.Count)).Clear
    End With
         .Range("A2", .Cells(1).End(xlDown)).Value = .Parent.Name
         .AutoFit
  End With
    ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".csv", ".xlsx"), 51
    ActiveWorkbook.Close
      CSV = Dir
Wend
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      MsgBox N & " csv file" & IIf(N > 1, "s", "") & " converted", vbInformation, "    Done !"
End Sub

You may Like it !

But result should be exactly same as previous code which erase
data only from D1 to D11, as D12:D13 cells are already blank !

If it's not the expected result, unlock your conversation
and attach your sensitive csv text file …
​

 

[RAM72]

As I can't answer to your conversation 'cause you locked it,
first try this mod based upon your screenshot (so untested) :

Sub Macro1_1()
      Application.DisplayAlerts = False
      Application.ScreenUpdating = False
      CSV$ = Dir(ThisWorkbook.Path & "\*.csv")
While CSV > ""
    N& = N& + 1
    Workbooks.OpenText ThisWorkbook.Path & "\" & CSV, xlWindows, , xlDelimited, xlTextQualifierNone, Comma:=True, DecimalSeparator:="."
  With ActiveSheet.UsedRange.Columns
         .Range("D1:D" & .Cells(1).End(xlDown).Row - 1).Clear
    With .Resize(, .Count + 1)
         .Item(.Count).Formula = "=D1="""""
         .Sort .Cells(.Count), xlAscending, Header:=xlNo
          Union(.Item(.Count), .Rows(Application.Match(True, .Item(.Count), 0) & ":" & .Rows.Count)).Clear
    End With
         .Range("A2", .Cells(1).End(xlDown)).Value = .Parent.Name
         .AutoFit
  End With
    ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".csv", ".xlsx"), 51
    ActiveWorkbook.Close
      CSV = Dir
Wend
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      MsgBox N & " csv file" & IIf(N > 1, "s", "") & " converted", vbInformation, "    Done !"
End Sub

You may Like it !

But result should be exactly same as previous code which erase
data only from D1 to D11, as D12:D13 cells are already blank !

If it's not the expected result, unlock your conversation
and attach your sensitive csv text file …
​

Needful done

untested macro see attached debug errors

 

[Marc L]

Sub Macro1_2()
      Application.DisplayAlerts = False
      Application.ScreenUpdating = False
      CSV$ = Dir(ThisWorkbook.Path & "\*.csv")
While CSV > ""
    N& = N& + 1
    Workbooks.OpenText ThisWorkbook.Path & "\" & CSV, xlWindows, , xlDelimited, xlTextQualifierNone, Comma:=True, DecimalSeparator:="."
        With ActiveSheet.UsedRange
            C& = .Columns(.Columns.Count).Column + 1
            R& = .Rows(.Rows.Count).Row
        End With
    With Cells(1).Resize(R, C).Columns
        .Range("D1:D" & .Cells(1).End(xlDown).Row - 1).Clear
        .Item(C).Formula = "=D1="""""
        .Sort .Cells(C), xlAscending, Header:=xlNo
         Union(.Item(C), .Rows(Application.Match(True, .Item(C), 0) & ":" & R)).Clear
        .Range("A2", .Cells(1).End(xlDown)).Value = .Parent.Name
        .AutoFit
    End With
        ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".csv", ".xlsx"), 51
        ActiveWorkbook.Close
      CSV = Dir
Wend
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      MsgBox N & " csv file" & IIf(N > 1, "s", "") & " converted", vbInformation, "    Done !"
End Sub

You should Like it !
​

 

[RAM72]

Thank you for assistance and patience Marc working flawlessly .

Code is fantastic

You remove me from a muddy situation

 

[Marc L]

This thread is just the proof that an initial crystal clear explanation
with an attachment reflecting exactly the layout of real data are needed
to solve this kind of coding request !

I was in the classic trap of UsedRange
but 'cause I was blind without a real source file …
​