Hi all.
Simply, a mere technical question about the Excel VBO Object. We€™re dealing with a CSV exported file from an application that we need to use. The file is not a €œcomma-separated€ fields formatted file, but a €œsemicolon-separated€€¦. What to do?
Three different approaches ( €œImport as CSV€, €œLoad CSV into Collection€ and €œImport Workbook (Fast)€ ) do not work properly. May be we could execute a code€¦ well, simply one single line, to convert the CSV into XLSX, but I can not find out how.
See below a VBS sample code I€™ve found out there to see whether it€™s possible to use it anywhere into the Excel VBO. The relevant line is about: "TextToColumns".
[BEGIN :: VBS]
Dim myXL
Const xlDelimited = 1
Const xlOpenXMLWorkbook = 51
Set myXL = CreateObject("Excel.Application")
myXL.Visible=False
For Each file In WScript.Arguments
myXL.WorkBooks.OpenText file, , , xlDelimited, , , , , True 'Change the source path as needed.
myXL.DisplayAlerts=False
myXL.Sheets(1).Columns("A").TextToColumns myXL.Range("A1"), xlDelimited, , , , True 'semicolon-delimited
myXL.ActiveWorkbook.SaveAs Replace(file, ".csv", ""), xlOpenXMLWorkbook 'Change the destination path as needed.
myXL.DisplayAlerts=True
myXL.ActiveWorkbook.Close False
Next
myXL.Quit
Set myXL = Nothing
[END :: VBS]
Thanks in advance, as usual, for your help.
- Arturo
