AstridM
07-26-2022, 12:37 PM
Hello,
I have a large number of files I need to convert from csv to excel and I have follow this link https://www.extendoffice.com/documents/excel/4615-excel-batch-convert-csv-to-xls-xlsx.html and the code is working perfectly.
Sub CSVtoXLS()
'UpdatebyExtendoffice20170814
Dim xFd As FileDialog
Dim xSPath As String
Dim xCSVFile As String
Dim xWsheet As String
Application.DisplayAlerts = False
Application.StatusBar = True
xWsheet = ActiveWorkbook.Name
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
xFd.Title = "Select a folder:"
If xFd.Show = -1 Then
xSPath = xFd.SelectedItems(1)
Else
Exit Sub
End If
If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
xCSVFile = Dir(xSPath & "*.csv")
Do While xCSVFile <> ""
Application.StatusBar = "Converting: " & xCSVFile
Workbooks.Open Filename:=xSPath & xCSVFile
ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xlsx", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows(xWsheet).Activate
xCSVFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
The issues is that the csv has a delimiter and I want to have it in different columns, I have recorded a macro to know how to do text to columns
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1)), Array(24, 1)), Array(25, 1)) _
Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1)), Array(31, 1)), Array(32, 1)) _
Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1)), Array(38, 1)), Array(39, 1)) _
TrailingMinusNumbers:=True
I am trying to join both codes, but I am getting errors. Not sure how to join them
thanks
Astrid
I have a large number of files I need to convert from csv to excel and I have follow this link https://www.extendoffice.com/documents/excel/4615-excel-batch-convert-csv-to-xls-xlsx.html and the code is working perfectly.
Sub CSVtoXLS()
'UpdatebyExtendoffice20170814
Dim xFd As FileDialog
Dim xSPath As String
Dim xCSVFile As String
Dim xWsheet As String
Application.DisplayAlerts = False
Application.StatusBar = True
xWsheet = ActiveWorkbook.Name
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
xFd.Title = "Select a folder:"
If xFd.Show = -1 Then
xSPath = xFd.SelectedItems(1)
Else
Exit Sub
End If
If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
xCSVFile = Dir(xSPath & "*.csv")
Do While xCSVFile <> ""
Application.StatusBar = "Converting: " & xCSVFile
Workbooks.Open Filename:=xSPath & xCSVFile
ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xlsx", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows(xWsheet).Activate
xCSVFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
The issues is that the csv has a delimiter and I want to have it in different columns, I have recorded a macro to know how to do text to columns
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1)), Array(24, 1)), Array(25, 1)) _
Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1)), Array(31, 1)), Array(32, 1)) _
Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1)), Array(38, 1)), Array(39, 1)) _
TrailingMinusNumbers:=True
I am trying to join both codes, but I am getting errors. Not sure how to join them
thanks
Astrid