topic Re: Save Excel worksheet as csv in Product Forum

Save Excel worksheet as csv

JeremyRTDean — Wed, 23 Feb 2022 23:43:00 GMT

Newbee question so an example would be helpful...

I'm looking for an action that saves a populated Excel worksheet as a .csv file.

I expect I need to extend the existing VBO Excel SaveWorkbookAs action which uses the code:
wb.SaveAs(filename)

to somehow use the VB parameter:

xlCSV

Any tips on how I do that (or more specifically, the syntax)?

------------------------------
Jeremy Dean
------------------------------

RE: Save Excel worksheet as csv

ewilson — Thu, 24 Feb 2022 00:45:00 GMT

Hi @Jeremy Dean,

Change the referenced line in the screen shot below to this:

wb.SaveAs(filename, 6) ' 6 is the equivilent of the xlCSV enum entry.

You might want to actually create a copy of the action first though. Assuming you want to change the action Save Workbook As, right click on the tab and choose Duplicate. Give the new action a different name, like Save Workbook As CSV.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Save Excel worksheet as csv

JeremyRTDean — Thu, 24 Feb 2022 02:41:00 GMT

Thanks Eric,

That was quick and helpful. Using the enumeration value worked.

Can I ask why we use the enumerated value 6 and not the parameter 'xlCSV'?

So this works:
wb.SaveAs(filename, 6)

But this does not:
wb.SaveAs(filename, xlCSV)

There is a handy list of the Excel Save As types here:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

------------------------------
Jeremy Dean
------------------------------

RE: Save Excel worksheet as csv

ewilson — Thu, 24 Feb 2022 03:04:00 GMT

@Jeremy Dean,

The XlFileFormat enum is defined in the Microsoft.Office.Interop.Excel namespace of the DLL by the same name. I don't recall specifically, but I don't think that DLL/namespace are defined on the VBO. If they were, you should be able to use the enum directly.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

Re: RE: Save Excel worksheet as csv

Arman_K — Fri, 26 Jul 2024 09:21:22 GMT

@ewilson What if I want to save this file as a CSV UTF-8 file format

Re: RE: Save Excel worksheet as csv

ewilson — Fri, 26 Jul 2024 14:10:14 GMT

@Arman_K we're updated the asset to include an additional input parameter on the Save Workbook As action to let you specify one of the supported formats from Excel. Should be published later today.

Cheers,
Eric

Re: RE: Save Excel worksheet as csv

ewilson — Fri, 26 Jul 2024 17:36:30 GMT

@Arman_K version 10.3.6 of the Excel VBO has been published with a new optional input parameter on the Save Workbook As and Save Current Workbook As actions. The new parameter, called File Format, takes a numeric input that corresponds to the format you want to use. You can find a list of the Excel supported formats here. For UTF8 CSV you'd be looking at 62.

Cheers,
Eric

Re: RE: Save Excel worksheet as csv

FrankieTEWV — Mon, 21 Oct 2024 12:23:05 GMT

Hi Eric,

I need to save my excel csv file as an xlsm file, as i need to use the oldeb actions.

i checked out the link you sent for excel supported formats and xlsm = 52

i have made a duplicate of the "save workbook as" action in excel vbo and input 52 into the code as you suggested above - see below code

but I'm getting this error when running the action? is my code wrong or do you have any ideas why its not working?

Thanks

Frankie

Re: RE: Save Excel worksheet as csv

FrankieTEWV — Mon, 21 Oct 2024 12:30:50 GMT

sorry just to add i did see a save workbook as xlsm action under the MS Excel Extended vbo - ErnstYoung - so checked the code out on that and could see it was set as FileFormat:=52

I did update the cod eon the one i have created but still get the same error

Re: RE: Save Excel worksheet as csv

ewilson — Mon, 21 Oct 2024 13:33:34 GMT

Hi @FrankieTEWV

Bit of an odd error there. Not sure if it's saying there's an issue in the workbook or within the VBO trying to locate the instance. In your process are you creating a new instance of Excel or are you attaching to an existing instance?

Cheers,
Eric

Re: Save Excel worksheet as csv

VanithaK — Fri, 27 Dec 2024 10:25:01 GMT

Hi,

We were using MS office 365 Proplus and for xlCSVUTF8 = 62 to save the excel into CSV.

But now same code is not working for MS Office 2016 Professional Plus.

Can you please help us what should be used here. And after saving it, we are reading the CSV and writing it into CSV as UTF8 using Write Text file as UTF8.