Convert CSV to xlsx, in vba

Asked

Viewed 335 times

-1

I am using this code to convert from CSV to XLSX. But it is ignoring the OtherChar:=";" and separating the columns in the comma.


Dim xlApp As Object 'Excel.Application
 Dim xlBook As Object 'Excel.Workbook
 Dim xlSht As Object 'Excel.Worksheet

 Set xlApp = CreateObject("Excel.Application")

 '################### teste de conversão de tipo de ficheiro cvs to xlsx







 Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
  objExcel.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open(TextBox1.text)
Set objWorksheet1 = objWorkbook.Worksheets("E60POR03")
objWorksheet1.Columns("$A:$A").TextToColumns destination:=objWorksheet1.Range("A1"), dataType:=1, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, 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)), _
            TrailingMinusNumbers:=True


        '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)), _

'Save Spreadsheet, 51 = Excel 2007-2010
 objExcel.ActiveWorkbook.SaveAs FileName:=Left(TextBox1.text, InStrRev(TextBox1.text, ".") - 1), FileFormat:=51

' objExcel.Workbooks.Close
 objExcel.Quit

 Set objExcel = Nothing
 Set objWorkbook = Nothing
 Set objWorksheet1 = Nothing
vba

1 answer

0

The answer is in your own code. Note the following line where you have instructed what Excel has to consider and disregard:

TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:=";",

You’re telling him to ignore the semicolon in the attribute Semicolon with the False and telling him to use the comma in the attribute Comma setado in True. Just change this and remove the fields Other and OtherChar.

Stay like this:

Dim xlApp As Object 'Excel.Application
Dim xlBook As Object 'Excel.Workbook
Dim xlSht As Object 'Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")

'################### teste de conversão de tipo de ficheiro cvs to xlsx

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open(TextBox1.text)
Set objWorksheet1 = objWorkbook.Worksheets("E60POR03")
objWorksheet1.Columns("$A:$A").TextToColumns destination:=objWorksheet1.Range("A1"), dataType:=1, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, _
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)), _
TrailingMinusNumbers:=True

'Save Spreadsheet, 51 = Excel 2007-2010
objExcel.ActiveWorkbook.SaveAs FileName:=Left(TextBox1.text, InStrRev(TextBox1.text, ".") - 1), FileFormat:=51

' objExcel.Workbooks.Close
objExcel.Quit

Set objExcel = Nothing
Set objWorkbook = Nothing
Set objWorksheet1 = Nothing

Browser other questions tagged vba

You are not signed in. Login or sign up in order to post.