Running text2column for column A:A

Question

I am quite new to VBA scripting, but I have to perform the following actions:

convert a csv file to xls one
in the saved xls file to perform the following actions: 1. To run Text To Columns for the data 2. To define the type of each column ( amount , date, text). 3. To make the first 0 in Amount&Date columns be visible ( e.g. "04521.6823" & for date "05/02/2020"). Here I plan to use .NumberFormat option.

I succesfully converted the file, but cannot perform Text2Column part. My data is only in column A, separator ";", only on sheet1. Here is the latest code I ran:

 Sub text2columns()
    Dim rg As Range

    Set rg = Range("A:A").CurrentRegion

    rg.TextToColumns Destination:=Range("A:A"), ConsecutiveDelimiter:=True, DataType:=xlDelimited, Space:=True

    End Sub

Can anyone help me? I know questions are basic, but need some help to continue. Thank you!

1 - 2. Would you like to run `TextToColumn` for all the file columns? If yes, in which way would you like to format each of them? Is there a logic to be applied in order to choose between `Number`, `Text`, `Date` or something else? 3. Which column would be `Amount&Date` one? How the string `"04521.6823"` was/to be calculated? The Long value for "05/02/2020" would be "43953" in case of "mm/dd/yyyy" format, or "43866", in case of "dd/mm/yyyy". So, based on what logic and data is it calculated/built? The format aspect is besides that... — FaneDuru, Feb 15 '20 at 10:24
@FaneDuru - Once I convert the file from csv to xls, all the text is located in column A ( vary but data is up to 20 000-40 000 lines ) . Then , after performing text to column, the result should be 20 columns ( this doesn't change, they are always 20). Then , columns with TEXT format are 1-8 & 10th; columns with DATE format are 12-15 & 18th; columns with amount format - 9,11,16,17,19, 20. All the data is directly exported, no calculations within the file. — YN., Feb 15 '20 at 15:11
What do you mean by "Once I convert the file from csv to xls"? What "conversion" is needed instead of just opening it according to the .csv delimiter? You can do that, formatting all column as necessary, too. And just saying "columns with DATE format" you did not described enough the needed format. `TextToColumns` offers 6 date format types and it is mandatory to choose one of them... What do you mean by "exporting"? Isn't it a simple SaveAs (.csv)? Or imputing of all cells in an array and writing it as text? If not, can you explain how to you intend to proceed for your file processing? — FaneDuru, Feb 15 '20 at 16:23

score 0 · Answer 1 · answered Feb 15 '20 at 09:39

0

You have mentioned Destination:=Range("A:A"). Also, your input and output range cannot be same. I think, it shouldn't be a column, only a cell

Please check if Destination:=Range("B1") works.

answered Feb 15 '20 at 09:39

Naresh

2,984
2
9
15

Thanks -yes, indeed ny mistake here. It should not be A:A, but A1 – YN. Feb 16 '20 at 12:11

score 0 · Accepted Answer · answered Feb 15 '20 at 09:50

Fomramt Style ~> 1: tab, 2: comma, 3:space, 4:semiclone, 5:None, 6:User's Letter

Sub TransToXLSFromCSV()
Dim vFile As Variant
Dim vDB
Dim fn As String
Dim strPath As String
Dim i As Long
Dim Wb As Workbook
    strPath = ThisWorkbook.Path
    vFile = Application.GetOpenFilename("Select Text Files *.txt,*.txt;*.csv", _
      Title:="Select Text Files!", MultiSelect:=True)
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Application.ScreenUpdating = False

    For i = 1 To UBound(vFile)
        fn = Dir(vFile(i))
        fn = Left(fn, Len(fn) - 4)

         Set Wb = Workbooks.Open(Filename:=vFile(i), Format:=2) '<~~ Foramt style comma
         vDB = Wb.ActiveSheet.UsedRange
         Wb.Close
         Set Wb = Workbooks.Add
         With Wb
            .ActiveSheet.Range("a1").Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
            .SaveAs Filename:=strPath & "\" & fn & ".xlsx"
            .Close (0)
         End With
    Next i
    Application.ScreenUpdating = True
End Sub

FaneDuru · Answer 3 · 2020-02-16T17:18:44.217

Try this code, please:

Sub text2columns()
    Dim rg As Range, arrFlInf(19) As Variant, i As Long
    For i = 0 To UBound(arrFlInf)
        Select Case i
            Case 1 - 8, 10   'Text
                arrFlInf(i) = Array(i + 1, 2)
            Case 12 - 15, 18 'date (MDY)
                arrFlInf(i) = Array(i + 1, 3)
            Case Else        'General
                arrFlInf(i) = Array(i + 1, 1)
        End Select
    Next i
    Set rg = Range("A:A")

    rg.TextToColumns Destination:=Range("A1"), _
        ConsecutiveDelimiter:=False, _
        DataType:=xlDelimited, _
        Semicolon:=True, _
        fieldInfo:=arrFlInf
End Sub

If you do not need the date format to be of type "MDY", you must change it in one of the next categories: 4 = DMY, 5 = YMD, 6 = MYD, 7 = DYM and 8 = YDM.

Running text2column for column A:A

3 Answers3

Linked