Saving a CSV-file as a Excel-file

[driessen]

Hello,

I'm starting from a CSV-file which I need to save as an Excel-file (XLS).

I'm trying to this by using these code :

Code: Select all  Expand view

* cBest = CSV-file
oBook  := oExcel:WorkBooks:Open(TRIM(cBest))
oBook:SaveAs("Test.xls")
oBook:Close()
oBook := oExcel:WorkBooks:Open("Test.xls")

Unfortunately, the result is not good. All I get is every line of the CSV-file in a cell in the first column.

I need to have the fields seperated. How can I establish that?

Thanks a lot in advance for any help.

[Marc Vanzegbroeck]

Michel,

Are you using a Dutch version of excel?
If so, the seperator is ";" instead of ",".
You have to change the seperator in the CSV-file, of change the seperator in excel.
I usual change it with strtran() in the csv-file, before reading it to excel.

[driessen]

Mark,
I have no problem in read a CSV-file. That is going fine.
I only want to save it as an Excel-file.

I use a similar function in Word for saving a Word-document as a PDF-file. So, I add "wdFormatPDF" to the line "SaveAs". This "wdFormatPDF" is defined at the beginning of my PRG-file.
I simply can't find the similar function for Excel.

[Marc Vanzegbroeck]

Michael,

You say that you don't have any problem reading the CSV-file in excel, but isn't is so that when you read it, that everything is in the first column?

[driessen]

Mark,

No problem at all.

The CSV-files come from Belgian banks and the fields are separated by a semi-colon.

[Marc Vanzegbroeck]

Maybe specify the FileFormat since:

For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Code: Select all  Expand view

.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)

[driessen]

Mark,

Thanks a lot.

But that is my question : how do I define the fileformat?

[Marc Vanzegbroeck]

Michael,

Code: Select all  Expand view

oBook:SaveAs("Test.xls",xlExcel8)

Format saveas
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

List fileformat:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

[Jimmy]

Mark,
I use a similar function in Word for saving a Word-document as a PDF-file. So, I add "wdFormatPDF" to the line "SaveAs". This "wdFormatPDF" is defined at the beginning of my PRG-file.

I simply can't find the similar function for Excel.

you make a mistake when think you can use Excel SaveAs(PDF).
you can only use Constant from https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

PDF is a "End-User" Format that mean you can't load it again to Excel or Word without "Konverter"
you can "print" a Excel Sheet as PDF but not SaveAs()