[SOLVED] csv to excelfile conversion via VBA

csv to excelfile conversion via VBA

I'm trying to write a function which converts a csv file into an Excel file. I'm using Access 2021 and my problem is concerning the line with the Opentextfile as shown here:

Private Sub CreateExcel(strCSVPath As String, strXLSXSpath As String)
    Dim xlApp As Object
    Dim wb As Object

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False

    Set wb = xlApp.Workbooks.Add

    With wb.Worksheets(1)
        .Opentext strCSVPath, xlTextimportdelimited, Comma:=True
        .Range("A1").CurrentRegion.TexttoColumns  Destination:=.Range("A1"), dataType:=xlGeneral
    End With

    wb.SaveAs strXLSXSpath, FileFormat:=xlopenXMLWorkbook
    wb.Close Savechanges:=False
    xlApp.Quit Savechanges:=False

    Set wb = Nothing
    Set xlApp = Nothing
End Sub

I appreciate any help. Thanks in advance.

Solution

Please, try this way:

Private Sub CreateExcelSess(strCSVPath As String, strXLSXSpath As String)
 Dim wb As Object

 With CreateObject("Excel.Application") 'False visibility is default
    .Workbooks.OpenText fileName:=strCSVPath, startRow:=1, DataType:=1, Comma:=True
    Set wb = .ActiveWorkbook
    wb.saveas fileName:=strXLSXSpath, FileFormat:=51 '(xlWorkbookDefault, xlOpenXMLWorkbook)
    wb.Close False
    .Quit
 End With
End Sub

Excel does not keep the csv columns saved format. So, if you have some numbers, date and want to be string or something else, you must tell us (which columns) to use another parameter dealing with format...