Share via

Joining two pieces of code

Astrid o 1 Reputation point
2022-07-26T14:35:13.447+00:00

Hello,
I have a large number of files I need to convert from csv to excel.
I have tried the code on this article and it is working. https://www.extendoffice.com/documents/excel/4615-excel-batch-convert-csv-to-xls-xlsx.html 

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

my problem is that the csv it is separated by a delimiter "|", and I need to convert it to excel in columns.
I tried recording the macro on how to convert to columns and I get 

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

but when I tried to add the code, I get errors, this is what I did. 

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

Where am I going wrong?
Thanks

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,595 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oskar Shon 866 Reputation points MVP
    2022-11-08T21:26:48.693+00:00

    What if you kill this lines: 

    , 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

    Regards

    0 comments