另存为 xls 工作簿时删除所有宏/vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16398441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete all Macros/vba when save as xls workbook
提问by xyz
I am using a procedure that creates a file and copies my workbook (xlsm) and saves as a xls workbook to the created file, and this is working well.
我正在使用创建文件并复制我的工作簿 (xlsm) 并将 xls 工作簿另存为创建的文件的过程，这运行良好。
I need to remove all Macros and vba when the save asis exacuted, i.e I need to remove the Macros/vba from the workbook being saved NOT the original workbook.
当保存为执行时，我需要删除所有宏和 vba ，即我需要从正在保存的工作簿中删除宏/vba，而不是原始工作簿。
I know I could save it as a xlsx workbook to remove all Macros and vba but I need the workbook to be a Macro/vba free xls workbook.
我知道我可以将其另存为 xlsx 工作簿以删除所有宏和 vba，但我需要该工作簿是一个无宏/vba 的 xls 工作簿。
I have Google'ed but did not find anything I could use, will continue to look and post back if I get this figured out.
我已经谷歌搜索但没有找到任何我可以使用的东西，如果我弄清楚了，我会继续查看并发回。
回答by xyz
I found this here:
我在这里找到了这个：
http://enholm.net/index.php/blog/vba-code-to-transfer-excel-2007-xlsx-books-to-2003-xls-format/
http://enholm.net/index.php/blog/vba-code-to-transfer-excel-2007-xlsx-books-to-2003-xls-format/
It searches through a dirictory looking for xlsxfiles and changes them to xlsfiles
它在目录中搜索xlsx文件并将它们更改为xls文件
I think though it can be changed to look for xlsmfiles and change them to xlsfiles as well.
我认为虽然可以将其更改为查找xlsm文件并将它们更改为xls文件。
When I run it I get:
当我运行它时，我得到：
Run-Time error '9' Subscript out of range
Debug
Sheets("List").Cells(r, 1) = Coll_Docs(i)
is highlighted in yellow
I do not know enough about vba to figure out what is not working. Thanks
我对 vba 的了解不够，无法弄清楚什么不起作用。谢谢
Sub SearchAndChange()
Dim Coll_Docs As New Collection
Dim Search_path, Search_Filter, Search_Fullname As String
Dim DocName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
Search_path = ThisWorkbook.Path & "0 Compiled Repository\May_2013"
Search_Filter = "*.xlsx"
Set Coll_Docs = Nothing
DocName = dir(Search_path & "\" & Search_Filter)
Do Until DocName = ""
Coll_Docs.Add Item:=DocName
DocName = dir
Loop
r = 1
For i = Coll_Docs.Count To 1 Step -1
Search_Fullname = Search_path & "\" & Coll_Docs(i)
Sheets("List").Cells(r, 1) = Coll_Docs(i)
Call changeFormats(Search_path, Coll_Docs(i))
r = r + 1
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
'**************************************************************
'* Changes format from excel 2007 to 2003
'***************************************************************
Sub changeFormats(ByVal dir As String, ByVal fileName As String)
Workbooks.Open fileName:=dir & fileName
ActiveWorkbook.SaveAs fileName:=dir & Replace(fileName, "xlsx", "xls"), FileFormat:=xlExcel8
ActiveWindow.Close
End Sub
