excel VBA将一个目录下的所有xls文件批量转换为xlsx文件

office教程网 2023-06-16 01:35:00 329

后台-插件-广告管理-内容头部广告位

后台-系统设置-扩展变量-手机广告位-内容正文顶部

摘要： Option Explicit
Sub xlsTOxlsx() Dim strFilePath As String, strFileName As String, strFileType As String Dim aIndex As Long, arrFileName() As String, strNewName As String
'设置文件扩展名标识文件类型 strFile...

Option Explicit

Sub xlsTOxlsx()
Dim strFilePath As String, strFileName As String, strFileType As String
Dim aIndex As Long, arrFileName() As String, strNewName As String

'设置文件扩展名标识文件类型
strFileType = ".xls"

如何利用excel快速提取身份证号码中出生日期
用Excel快速提取身份证号码中出生日期只需在旁边输入一个模板，然后按快捷键Ctrl E就能快速的提取出生年月。excel提取身份证号码中出生日期

On Error Resume Next
'设置文件夹路径
strFilePath = CreateObject("shell.application").BrowseForFolder(0, "请选择文件夹", 0).self.Path
If Err <> 0 Or InStr(1, strFilePath, "::") > 0 Then
Err = 0
Exit Sub
End If

'开始搜索文件
strFileName = Dir(strFilePath & "*.*")
Do While strFileName <> ""
If LCase(Right(strFileName, Len(strFileType))) = LCase(strFileType) Then
ReDim Preserve arrFileName(aIndex)
arrFileName(aIndex) = strFileName
aIndex = aIndex 1
'Debug.Print strFileName
End If
strFileName = Dir
DoEvents
Loop
If aIndex = 0 Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For aIndex = LBound(arrFileName) To UBound(arrFileName)
strNewName = Mid(arrFileName(aIndex), 1, Len(arrFileName(aIndex)) - Len(strFileType)) & ".xlsx"
Workbooks.Open strFilePath & arrFileName(aIndex)
ActiveWorkbook.SaveAs Filename:=strFilePath & strNewName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks(strNewName).Close False '关闭工作簿
Kill strFilePath & arrFileName(aIndex)
DoEvents
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "操作完成，共为您转换了 " & UBound(arrFileName) 1 & " 个文件。", vbOKOnly, "完成"
End Sub