Save Excel worksheet as csv

[JeremyRTDean]

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
------------------------------

[ewilson · Accepted solution]

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
------------------------------

[JeremyRTDean]

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
------------------------------

[ewilson]

@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
------------------------------

[Arman_K]

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

[ewilson]

@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 

[ewilson]

@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  

[FrankieTEWV]

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

[FrankieTEWV]

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 

[ewilson]

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