List separator when saving csv file

Add-in Express™ Support Service
That's what is more important than anything else

List separator when saving csv file
 
Lakshman Kumar


Guest


Hi,

I has an add-in where I am handling Before Save event for Excel and in the event cancelling the current event, If it is saveAs show saveAs dialog box if not calling Workbook.Save().

But I has "|" symbol as List separator in Control Panel | Region and Language | Additional Settings | Numbers | List Separator.

1. In Workbook.SaveAs() function last parameter (local) is there to use system based parameter. By using this, I was able to use "|" (or whatever mentioned as List Separator)

But in Workbook.Save() function it is not the case as when opening the saved file in notepad, I can see "," (default one) as separator instead of "|".

Is there any flags or options to be set before calling Workbook.Save() for csv file, so Excel will use the correct separator?

Thanks,
Lakshman.
Posted 20 Aug, 2019 04:13:53 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Hello Lakshman,

I've understood you so that you call SaveAs(Local:=true) while having a custom separator specified in the Control Panel and this does work; you find the custom separator is ignored while calling Save() to save the already saved workbook representing the .CSV file. Is this description correct?

Assuming that it is correct, I've reproduced this issue using these VBA macros and a test workbook (the macros are in another workbook to prevent interference):


Sub SaveAsCsv()
Application.Workbooks("Book1.xlsx").SaveAs "test.csv", xlCSV, , , , , , , , , , True
End Sub

Sub SaveTheCsv()
Application.Workbooks("test.csv").Save
End Sub


When I switch to the Excel UI after running the first method, I see the following warning message: "Possible Data Loss: Some features might be lost if you save this workbook in the comma-delimited (.csv) format. To preserve these features, save it in an Excel file format."

I suppose this warning may apply to your case. Anyway, you can use SaveAs again to save the correct separator:

Sub SaveAsCsvAgain()
Application.Workbooks("test.csv").SaveAs "test.csv", xlCSV, , , , , , , , , , True
End Sub



Andrei Smolin
Add-in Express Team Leader
Posted 20 Aug, 2019 05:37:25 Top
Lakshman Kumar


Guest


Hi Andrei,

Thanks for the reply. When saving xls/xlsx to csv, the UI shows warning saying that "Possible loss...", but it is not the case. Even I opened csv file, edited and pressed "Ctrl + S", then as I am calling Workbook.Save(), there is no way to mention to use "local" option (as you know but workbook.SaveAs() has the option).

So it is overwriting the List separator mentioned in Control Panel | Region and Language | Additional Settings | Numbers | List Separator with Comma separator.

As there is no other way (At least I don't know), as per your suggestions I am using SaveAs even for Save as follows.


object optional = Type.Missing;
object objTrue = true;
bool existingAlerts = ExcelApp.DisplayAlerts;
ExcelApp.DisplayAlerts = false;
workbook.SaveAs(workbook.FullName, workbook.FileFormat, optional, optional, optional, optional, 
 Excel.XlSaveAsAccessMode.xlNoChange, optional, optional, optional, optional, objTrue);
ExcelApp.DisplayAlerts = existingAlerts;


If I don't turn-off Alerts, then it is showing replace message with file existing, So as I know it is only save function am doing, at least it is working for me.

It might help somebody else.

Thanks,
Lakshman.
Posted 21 Aug, 2019 02:17:38 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Hello Lakshman,

Thank you for sharing your knowledge!


Andrei Smolin
Add-in Express Team Leader
Posted 21 Aug, 2019 04:33:32 Top