SQL Server VBS Excel SaveAs csv FileFormat 62/xlCSVUTF8 doesn´t work

isr3a4wc 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(169)

I have an VBS Script converting an excel file to csv format called by a SQL server. It contains:

Set objExcel = CreateObject("Excel.Application
Set objBook = objExcel.Workbooks.Open(objFile.Path, , True)
objBook.Worksheets(i).Activate
objBook.SaveAs file_out_start & i & ".csv", 6, , , True, False, , , , , , True

This returns a csv file encoded in ANSI. To further process the file I need the output to be in UTF-8 encoding.

Following the Microsoft documentation here and here , I tried changing the parameter 6 to 62 representing 'UTF8 CSV'. With this change the script stops working exiting with following error:

line: 36 (line of 'objBook.SaveAs')
error: The SaveAs method of the Workbooks object could not be executed. (translated)
Code: 800A03EC
source: Microsoft Excel

Can anyone help why this happens? If it is not possible to export with UFT-8 encoding directly is there a easy way to convert the files to UFT-8 afterward in the script?

sql-server

来源：https://stackoverflow.com/questions/68396100/vbs-excel-saveas-csv-fileformat-62-xlcsvutf8-doesn%c2%b4t-work

1条答案

按热度按时间

ccrfmcuu1#

I had a similar issue. I think the issue is Excel can only save a WorkSheet as a csv, not a WorkBook, and therefore needs to use Worksheet.SaveAs instead of Workbook.SaveAs. My code is below.

Link to Worksheet.SaveAs: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.saveas

Wscript.Echo "converting .xlsx to UTF8 .csv"
Set obj = CreateObject("Excel.Application")
p = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)
Set book = obj.Workbooks.Open(p & "\filename.xlsx")
obj.Application.Visible = False
Set sheet = book.Worksheets("Sheet1")
book.RefreshAll
obj.DisplayAlerts = False
sheet.SaveAs p & "\filename.csv", 62
book.Close False
Wscript.Echo "closing Excel.Application"
obj.Quit 'Not sure if this does anything but the internet said to use it

Wscript.Echo "End of vbs reached"

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server VBS Excel SaveAs csv FileFormat 62/xlCSVUTF8 doesn´t work

1条答案

相关问题

热门标签

最新问答