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!

Answer 1

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