我有大约200个标准的Excel 2003格式文件。
我需要将它们全部保存为Excel xml格式——基本上就是打开每个文件,选择另存为...,然后选择保存类型:XML电子表格
你知道有什么简单的自动化任务的方法吗?
我有大约200个标准的Excel 2003格式文件。
我需要将它们全部保存为Excel xml格式——基本上就是打开每个文件,选择另存为...,然后选择保存类型:XML电子表格
你知道有什么简单的自动化任务的方法吗?
Sub Convert_xls_Files()
Dim strFile As String
Dim strPath As String
With Application
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
End With
'Turn off events, alerts & screen updating
strPath = "C:\temp\excel\"
strFile = Dir(strPath & "*.xls")
'Change the path as required
Do While strFile <> ""
Workbooks.Open (strPath & strFile)
strFile = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close True
strFile = Dir
Loop
'Opens the Workbook, set the file name, save in new format and close workbook
With Application
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With
'Turn on events, alerts & screen updating
End Sub
打开所有文件,然后按ALT+F11进入宏编辑器,输入如下内容:
Sub SaveAllAsXml()
Dim wbk As Workbook
For Each wbk In Application.Workbooks
wbk.SaveAs FileFormat:=XlFileFormat.xlXMLSpreadsheet
Next
End Sub
然后按F5运行它。可能需要进行一些微调,因为我还没有测试过。
set xlapp = CreateObject("Excel.Application")
set fso = CreateObject("scripting.filesystemobject")
set myfolder = fso.GetFolder("YOURFOLDERPATHHERE")
set myfiles = myfolder.Files
for each f in myfiles
set mybook = xlapp.Workbooks.Open(f.Path)
mybook.SaveAs f.Name & ".xml", 47
mybook.Close
next
我还没有测试过,但它应该可以工作
Const xlXLSX = 51
REM 51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
REM 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
REM 50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
REM 56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
dim args
dim file
dim sFile
set args=wscript.arguments
dim wshell
Set wshell = CreateObject("WScript.Shell")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open( wshell.CurrentDirectory&"\"&args(0))
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE
objWorkbook.SaveAs wshell.CurrentDirectory&"\"&args(1), xlXLSX
objExcel.Quit
Wscript.Quit
最简单的方法是为一个文件记录宏,然后手动编辑宏以使用循环执行文件夹中的操作。在宏中,您可以使用标准VB函数获取目录中的所有文件并对其进行过滤。您可以查看http://www.xtremevbtalk.com/archive/index.php/t-247211.html以获取更多信息。