需要使用VBA将xls工作簿另存为xlsb [英] Need to save xls workbook As xlsb with VBA

问题描述

我使用宏来创建每日报告.宏将xls报告保存为xls历史记录.由于文件太大，我想将报告另存为xlsb.两个问题.我正在使用的宏脚本将运行，但以后无法打开xlsb文件.收到的消息是

I use a macro to create a daily report. The macro saves the xls report as xls historically. Due to large file size I want to save the report as xlsb. Two problems. The macro script i am using will run but I cannot open the xlsb file later. Message received is

"Excel无法打开文件'RDN Activity Report.xlsb'，因为文件格式或文件扩展名无效.请确认文件未损坏且文件扩展名与文件格式匹配.

"Excel cannot open the file'RDN Activity Report.xlsb' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

txtFileName = Format(Date - 1, "yyyymmdd")
ActiveWorkbook.SaveAs Filename:= _
    "\\Clt-stor01a\CA_Services\RDN Reports\ForUploadPrev\RDN Activity Report." & txtFileName & ".xlsb", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    txtFileName = Format(Date - 1, "yyyymmdd")

注意:我还需要一个脚本，当文件名中包含文件名且文件的日期为昨天的日期(例如"RDN Activity Report.20150726"

Note: I also need a script that can open a file when file name has date in file name and date of file is yesterday's date such as "RDN Activity Report.20150726"

推荐答案

使用SaveAs参数FileFormat:

• 50 = xlExcel12(2007-2013年的Excel二进制工作簿，带或不带宏， xlsb)

• 50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)

51 = xlOpenXMLWorkbook(不含2007-2013年的宏，xlsx)

51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)

52 = xlOpenXMLWorkbookMacroEnabled(在2007-2013年带有或不带有宏，xlsm)

52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)

56 = xlExcel8(Excel 2007-2013中为97-2003格式，xls)

56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

ActiveWorkbook.SaveAs "C:\temp\text.xlsb", fileformat:=50

这篇关于需要使用VBA将xls工作簿另存为xlsb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！