A batch of XLS files I edited with Office 2019 cannot be opened under Office 2010, but can be saved as XLSX, so I need to convert XLS to XLSX in batches.

At this point, my funny than colleagues to douyin to search for my tutorial……

You can use Excel’s built-in VBA without using third-party tools.

First, open the VBA editor:

  1. Menu bar development tools →Visual Basic

    If no development tool is available on the menu bar, right-click the function pane under the menu bar and choose Customize Function Pane. In a new window, select Development Tools on the right.

  2. Shortcut key: Alt + F11

Then paste the following code in the VBA editor:

'*********** Access all subfolders and files under the current folder, Dim iFile(1 To 100000) As String Dim count As Integer Sub xls2xlsx() iPath = ThisWorkbook.Path On Error Resume Next count = 0 zdir iPath For i = 1 To count If iFile(i) Like "*.xls" And iFile(i) <> ThisWorkbook.FullName Then MyFile = iFile(i) FilePath = Replace(MyFile, ".xls", ".xlsx") If Dir(FilePath, 16) = Empty Then Set WBookOther = Workbooks.Open(MyFile) Application.ScreenUpdating = False ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup: = False WBookOther. Close SaveChanges: = False 'solve the problem of can't Close file Application. The ScreenUpdating = True End If' note: If you don't want to keep the original XLS file, 'Kill (MyFile) End If Next End Sub Sub zdir(p) 'Set fs = CreateObject("scripting.filesystemobject") For Each f In fs.GetFolder(p).Files If f <> ThisWorkbook.FullName Then count = count + 1: iFile(count) = f Next For Each m In fs.GetFolder(p).SubFolders zdir m Next End SubCopy the code

Finally, click Run above (F5).

Note:

  1. XLS and file. XLSX will exist in the directory after execution. If you do not want to keep the File, change line 21:Kill(Myfile)The preceding comment is removed;
  2. If you want to batch convert XLSX to XLS, replace 12 lines of code with:FilePath = Replace(MyFile, ".xlsx", ".xls")Can.

This article was first published on hua Mo World, a personal website

Follow the public account to see more of my articles: Flower ink world