Use VBA to bulk convert Excel format from. xls to. xlsx

Problem Analysis:

Excel2007 the previous format is. xls, after which the format is. Xlxs. Open a separate Excel document and use the Save As feature to easily convert the format. But the face of hundreds of Excel tables so tired, search for a long time, also did not find a tool can be directly batch format conversion.

Finally, you can use VBA to implement the ability to convert the Excel format in bulk. Do not be frightened, VBA I do not understand, the code is from others, and now just know how to use, but this is enough to solve the problem

VBA Introduction:
1, VBA is a programming language, it relies on Office software, cannot run independently, through the VBA can achieve a variety of Office software operations automation.
2. Visual Basic for Applications (VBA) is a macro language of Visual Basic that can be used primarily to extend the application functionality of Windows. Excel documents developed with VBA in Excel need to turn on Excel's macro functionality when running in Excel, otherwise the VBA Automation feature of this document will be completely masked and the functionality of the document cannot be implemented.

Execution conditions:
1, Office2007 and above versions of Excel
2. Excel enables macros

Tool Download:
You can create your own new Excel, write VBA code, and code is behind it. You can also use the two I have already written, the following is the link in the Baidu Network disk:
"xls2xlsx.xlsm"
Link: http://pan.baidu.com/s/1mit3FGc Password:s57a
"convert2xlsx.xlsm"
Link: http://pan.baidu.com/s/1sluFttb password :pguv

Feature Description:
First of all, the two tools feature, you can download as needed.
Thexls2xlsx.xlsmwill traverse all. xls formatted files in the folder directory, including subdirectories , and perform the Save As action, and then the original file's current location Save as a file with the same name as the. xlsx format.
For example, there are 3. xls formatted files in the folder where the XLS2XLSX.XLSM was executed, including one subfolder, and 3. xls formatted files in the subfolder.

After execution, the following files are generated:

Theconvert2xlsx.xlsmwill traverse all. xls formatted files in the folder directory ( excluding subdirectories ), execute the Save as action, and then generate a folderunder the current path, xlsx, All the newly generated files are placed in this folder.
For example, there are 3. xls formatted files in the folder where the pre-CONVERT2XLSX.XLSM is executed

　　　　

After execution, the following files are generated:

　　　　　　　　

Execution method:
　　1, download any tool from the above, or create a new Excel;
2. Enable macro operations
In general, when you open Excel, the following warning pops up

　　　　

If this warning pops up, enable it directly, or if it does not appear, please refer to the following Baidu experience to enable macros http://jingyan.baidu.com/article/39810a23e39b9db637fda651.html
　　3. Write code to execute VBA
This step is certainly not a novice, detailed graphic introduction on the reference Baidu experience http://jingyan.baidu.com/article/09ea3ede265e8bc0aede39ba.html, I here only to make a simple explanation:
(a) Press the combo shortcut key on the ALT+F11 or right-click on any sheet tab and click "View Code" in the Popup context menu to enter the VBA editing environment
(b) If it is a downloaded my tool, you can skip this step; If you are creating new Excel, copy and paste one of the following two codes in the Code window in the VBA editing environment:
"Xls2xlsx.xlsm"

1 '*********** Access all subfolders and files under the current folder,2 DimIFile (1  to 100000) as String3 DimCount as Integer4 5 Subxls2xlsx ()6IPath =Thisworkbook.path7      on Error Resume Next8Count =09 Zdir IPathTen      fori =1  toCount One         IfIFile (i) Like"*.xls"  andIFile (i) <> thisworkbook.fullname Then AMyFile =IFile (i) -FilePath =Replace(MyFile,". xls",". xlsx") -             If Dir(FilePath, -) = Empty Then the                 SetWbookother =Workbooks.Open (MyFile) -Application.ScreenUpdating =False -ActiveWorkbook.SaveAs Filename:=filepath, Fileformat:=xlopenxmlworkbook, createbackup:=False -Wbookother.close savechanges:=False      'troubleshoot problems that cannot close files +Application.ScreenUpdating =True -             End If +         End If A     Next at End Sub -  - SubZdir (P)'access all subfolders and files under the current folder -   SetFS =CreateObject("Scripting.FileSystemObject") -    for  eachFinchFS. GetFolder (P). Files -     IfF <> Thisworkbook.fullname ThenCount = Count +1: IFile (count) =F in   Next -    for  eachMinchFS. GetFolder (P). Subfolders to Zdir M +   Next - End Sub

"Convert2xlsx.xlsm"

1 Subxls2xlsx ()2 DimFilePath, MyFile, IPath, Name, Outpath as String3IPath =Thisworkbook.path4Outpath =Dir(IPath &"\xlsx", vbdirectory)5 IfOutpath =""  Then6     MkDir(IPath &"\xlsx")7 End If8MyFile =Dir(IPath &"\*.xls")9 Ten IfMyFile <>""  Then One  Do A      on Error Resume Next -     IfMyFile = Thisworkbook.name ThenMyFile =Dir -Workbooks.Open (IPath &"\"&MyFile) theMyFile =Replace(MyFile,". xls",". xlsx") -Name ="\"&MyFile -FilePath = IPath &"\xlsx"&Name -Application.ScreenUpdating =False +ActiveWorkbook.SaveAs Filename:=filepath, Fileformat:=xlopenxmlworkbook, createbackup:=False -Workbooks (MyFile). CloseTrue +Application.ScreenUpdating =True AMyFile =Dir at Loop  whileMyFile <>"" - End If - End Sub

(c) Press the F5 shortcut key to execute this code or click the "Run Sub/UserForm" Command in the menu

PS: It is necessary to note that in the course of execution, do not do other things, or it may interrupt the operation of the program

Original articles, welcome reprint, reproduced please indicate the source!

Use VBA to bulk convert Excel format from. xls to. xlsx