打开一个Excel文件并另存为.XLS [英] Open an Excel file and save as .XLS

问题描述

我有以下代码，我希望它打开另存为.xlsx的文件，然后再次使用相同的文件名再次保存它们，但这一次与.xls文件相同，以便它们与Excel 2003兼容

I have the following code, I want it to open my files which are saved as .xlsx and simply save them again with the same filename but this time as a .xls file so that they are compatible with Excel 2003

Set app = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder("Y:\Billing_Common\autoemail").Files
  If LCase(fso.GetExtensionName(f)) = "xlsx" Then
    Set wb = app.Workbooks.Open(f.Path)

app.DisplayAlerts = False

wb.SaveAs "*.xls*"
wb.Close SaveChanges=True
app.Close
app.Quit

  End if

Set f = Nothing
Set fso = Nothing
Next

推荐答案

正如 Bathsheba 指出的那样，Set fso = Nothing和app.Quit属于脚本的末尾(循环之外).不过，还有更多错误.

As Bathsheba already pointed out, Set fso = Nothing and app.Quit belong at the end of the script (outside the loop). There are some more bugs, though.

• wb.SaveAs "*.xls*"

您不能将工作簿保存为通配符名称.如果要使用当前名称保存工作簿，只需使用wb.Save.否则，您将必须使用一个明确的名称(然后您还应该设置文件类型):

You can't save a workbook to a wildcard name. If you want to save the workbook under its current name, just use wb.Save. Otherwise you'll have to use an explicit name (you should also set the filetype then):

wb.SaveAs "new.xlsx", 51

或

wb.SaveAs "C:\path\to\new.xls", -4143

wb.Close SaveChanges=True

VBScript不支持命名参数(请参见此处).如果要在SaveChanges参数设置为True的情况下调用Close方法，则必须这样做:

VBScript doesn't support named parameters (see here). If you want to call the Close method with the SaveChanges parameter set to True you have to do it like this:

wb.Close True

app.Close

该应用程序对象没有Close方法.

The application object doesn't have a Close method.

不是错误，但是值得改进:

Not bugs, but things worth improving:

• app.DisplayAlerts = False应该在循环开始之前进行，除非您在循环内部也重新启用它.

• app.DisplayAlerts = False should go before the loop starts unless you re-enable it inside the loop as well.

我建议在创建应用程序对象后添加一行app.Visible = False.当您必须调试脚本时，只需将该值更改为True即可在桌面上显示该应用程序.这对发现错误有很大帮助.

I'd recommend adding a line app.Visible = False after you create the application object. When you have to debug your script you can simply change that value to True to show the application on your desktop. That helps a lot with finding bugs.

固定脚本:

Set app = CreateObject("Excel.Application")
app.Visible = False
app.DisplayAlerts = False

Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder("Y:\Billing_Common\autoemail").Files
  If LCase(fso.GetExtensionName(f)) = "xlsx" Then
    Set wb = app.Workbooks.Open(f.Path)

    wb.Save
    wb.Close True
  End if
Next

app.Quit
Set app = Nothing
Set fso = Nothing

这篇关于打开一个Excel文件并另存为.XLS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！