|
本帖最后由 0126 于 2021-6-28 19:51 编辑
For Each wbk In Application.Workbooks
If LCase(Right(wbk.name, 4)) = "xlsx" Then wbk.SaveAs wbk.FullName, FileFormat:=xlOpenXMLWorkbook
Next
应该是被保存为加载宏之类不显示的工作表,以上语句是让工作蒲是xlsx后缀名的工作蒲都另存为工作蒲。暂时不了解如何查看工作蒲真正的后缀类型,希望有路过大神指点
得大师指点。修改了下。下面代码是提取vbe工程里面的项目工作蒲,判断格式是否一致,不一致,另存为xlsx格式的文档
Sub 修正工作蒲()
Dim vb, wbk As Workbook, ar
For Each vb In Application.VBE.VBProjects
Set wbk = Workbooks(Mid(vb.Filename, VBA.InStrRev(vb.Filename, "\") + 1, 99))
ar = isworkbooks(wbk)
If ar(3) <> LCase(Mid(wbk.name, VBA.InStrRev(wbk.name, ".") + 1, 99)) Then wbk.SaveAs Left(wbk.FullName, VBA.InStrRev(wbk.FullName, ".")) & "xlsx", xlOpenXMLWorkbook, , , , , , xlLocalSessionChanges ' 另保存为
Next
End Sub
Function isworkbooks(wbk As Workbook)
dim st as string
st = wbk.FileFormat
arr = Array(-4158, "xlCurrentPlatformText\/-4158\/当前平台文本\/txt", -4143, "xlWorkbookNormal\/-4143\/常规工作簿\/xls", 2, "xlSYLK\/2\/符号链接格式\/slk", 4, "xlWKS\/4\/Lotus 1-2-3 格式\/wks", 5, "xlWK1\/5\/Lotus 1-2-3 格式\/wk1", _
6, "xlCSV\/6\/CSV\/csv", 7, "xlDBF2\/7\/Dbase 2 格式\/dbf", 8, "xlDBF3\/8\/Dbase 3 格式\/dbf", 9, "xlDIF\/9\/数据交换格式\/dif", 11, "xlDBF4\/11\/Dbase 4 格式\/dbf", _
14, "xlWJ2WD1\/14\/日语 1-2-3\/wj2", 15, "xlWK3\/15\/Lotus 1-2-3 格式\/wk3", 16, "xlExcel2\/16\/Excel 版本 2.0 (1987)\/xls", 17, "xlTemplate\/17\/Excel 模板格式\/xlt", 18, "xlAddIn\/18\/Microsoft Excel 97-2003 外接程序\/xla", _
19, "xlTextMac\/19\/Macintosh 文本\/txt", 20, "xlTextWindows\/20\/Windows 文本\/txt", 21, "xlTextMSDOS\/21\/MSDOS 文本\/txt", 22, "xlCSVMac\/22\/Macintosh CSV\/csv", 23, "xlCSVWindows\/23\/Windows CSV\/csv", _
24, "xlCSVMSDOS\/24\/MSDOS CSV\/csv", 25, "xlIntlMacro\/25\/国际宏\/无文件扩展名", 26, "xlIntlAddIn\/26\/国际外接程序\/无文件扩展名", 27, "xlExcel2FarEast\/27\/Excel 版本 2.0 中文 (1987)\/xls", 28, "xlWorks2FarEast\/28\/Microsoft Works 2.0 两端对齐格式\/wks", _
29, "xlExcel3\/29\/Excel 版本 3.0 (1990)\/xls", 30, "xlWK1FMT\/30\/Lotus 1-2-3 格式\/wk1", 31, "xlWK1ALL\/31\/Lotus 1-2-3 格式\/wk1", 32, "xlWK3FM3\/32\/Lotus 1-2-3 格式\/wk3", 33, "xlExcel4\/33\/Excel 版本 4.0 (1992)\/xls", _
34, "xlWQ1\/34\/Quattro Pro 格式\/wq1", 35, "xlExcel4Workbook\/35\/Excel 版本 4.0 工作簿格式 (1992)\/xlw", 36, "xlTextPrinter\/36\/打印机文本\/prn", 38, "xlWK4\/38\/Lotus 1-2-3 格式\/wk4", 39, "xlExcel5\/39\/Excel 版本 5.0 (1994)\/xls", _
40, "xlWJ3\/40\/日语 1-2-3\/wj3", 41, "xlWJ3FJ3\/41\/日语 1-2-3 格式\/wj3", 42, "xlUnicodeText\/42\/Unicode 文本\/无文件扩展名;*.txt", 43, "xlExcel9795\/43\/Excel 版本 95 和 97\/xls", 44, "xlHtml\/44\/HTML 格式\/.htm;.html", _
45, "xlWebArchive\/45\/Web 档案\/.mh;.mhtml", 46, "xlXMLSpreadsheet\/46\/XML 电子表格\/xml", 50, "xlExcel12\/50\/Excel 二进制工作簿\/xlsb", 51, "xlOpenXMLWorkbook\/51\/Open XML 工作簿\/xlsx", 52, "xlOpenXMLWorkbookMacroEnabled\/52\/启用 Open XML 工作簿宏\/xlsm", _
53, "xlOpenXMLTemplateMacroEnabled\/53\/启用 Open XML 模板宏\/xltm", 54, "xlOpenXMLTemplate\/54\/Open XML 模板\/xltx", 55, "xlOpenXMLAddIn\/55\/Open XML 外接程序\/xlam", 56, "xlExcel8\/56\/Excel 97-2003 工作簿\/xls", 60, "xlOpenDocumentSpreadsheet\/60\/OpenDocument 电子表格\/ods", _
61, "xlOpenXMLStrictWorkbook\/61\/Strict Open XML 文件\/xlsx", "&H3D", "xlOpenXMLStrictWorkbook\/&H3D\/Strict Open XML 文件\/xlsx", 62, "xlCSVUTF8\/62\/UTF8 CSV\/csv")
For x = LBound(arr) To UBound(arr) Step 2
If st = cstr(arr(x)) Then isworkbooks = Split(arr(x + 1), "\/"): Exit Function
Next
End Function
|
|