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.
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...