打开一个Excel文件并保存为.XLS

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

我有以下代码，我想让它打开以.xlsx保存的文件，然后简单地用相同的文件名再次保存它们，但这一次是.xls文件，以便它们与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

3 个解决方案

#1

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.

正如芭丝谢芭已经指出的，Set fso = Nothing and app.Quit属于脚本的末尾(在循环之外)。不过还有更多的bug。

wb.SaveAs "*.xls*"

白平衡。SaveAs“* xls *”

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.Save。否则，您将不得不使用显式名称(您还应该设置filetype):
```
wb.SaveAs "new.xlsx", 51
```
or

或
```
wb.SaveAs "C:\path\to\new.xls", -4143
```
wb.Close SaveChanges=True

白平衡。关闭SaveChanges = True

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:

VBScript不支持命名参数(参见这里)。如果你想用SaveChanges参数设置为True来调用Close方法，你必须这样做:
```
wb.Close True
```
app.Close

app.Close

The application object doesn't have a Close method.

应用程序对象没有关闭方法。

Not bugs, but things worth improving:

不是bug，而是值得改进的东西:

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

如果你在循环中重新启用它，则应该在循环开始之前执行。
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.

我建议在创建应用程序对象之后添加一个line app.Visible = False。当需要调试脚本时，只需将该值更改为True，就可以在桌面上显示应用程序。这对发现bug有很大帮助。

Fixed-up script:

修复脚本:

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

#2

Two serious bugs:

两个严重缺陷:

Set fso = Nothing should not be inside your loop: you'll need fso for the duration of the program.

设置fso =任何内容都不应该出现在循环中:在程序运行期间需要fso。
Also, drop app.Quit from the loop; keep Excel open until the very
end.

另外，从循环中退出app.Quit;保持Excel开放到最后。

Set f = Nothing is unnecessary (although benign); let the loop pick the values for you.

设f =没有必要(虽然是良性的);让循环为您选择值。

#3

Dim app, fso, file, fName, wb, dir 

dir = "d:\path\"

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

For Each file In fso.GetFolder(dir).Files
    If LCase(fso.GetExtensionName(file)) = "xlsx" Then  
    fName = fso.GetBaseName(file)

    Set wb = app.Workbooks.Open(file) 
    app.Application.Visible = False
    app.Application.DisplayAlerts = False
    app.ActiveWorkbook.SaveAs dir & fName & ".xls", 43
    app.ActiveWorkbook.Close 
    app.Application.DisplayAlerts = True 
    app.Application.Quit 

    End if
Next

Set fso = Nothing
Set wb = Nothing    
Set app = Nothing

wScript.Quit

#1