>

이전 .xls 에 많은 Excel 통합 문서가 있습니다.  체재. .xlsx 로 변환하고 싶습니다  VBA를 사용합니다. 다음 코드는이 작업을 수행하지만 다시 저장하려면 각 통합 문서를 열어야합니다.

Dim wbk As Workbook
Set wbk = Workbooks.Open(filename:="C:\some\example\path\workbook.xls")
wbk.SaveAs filename:="C:\some\example\path\workbook.xlsx", _
FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
wbk.Close SaveChanges:=False

각 통합 문서를 열지 않고도이 작업을 수행 할 수있는 다른 방법이 있습니까? 적어도 30-100 개의 통합 문서가 있으면 시간이 많이 걸립니다.

  • 답변 # 1

    다음은 원하는 것을 얻는 코드입니다.

    Sub ChangeFileFormat()
        Dim strCurrentFileExt   As String
        Dim strNewFileExt       As String
        Dim objFSO              As Object
        Dim objFolder           As Object
        Dim objFile             As Object
        Dim xlFile              As Workbook
        Dim strNewName          As String
        Dim strFolderPath       As String
        strCurrentFileExt = ".xls"
        strNewFileExt = ".xlsx"
        strFolderPath = "C:\Users\Scorpio\Desktop\New folder"
        If Right(strFolderPath, 1) <> "\" Then
            strFolderPath = strFolderPath & "\"
        End If
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.getfolder(strFolderPath)
        For Each objFile In objFolder.Files
            strNewName = objFile.Name
            If Right(strNewName, Len(strCurrentFileExt)) = strCurrentFileExt Then
                Set xlFile = Workbooks.Open(objFile.Path, , True)
                strNewName = Replace(strNewName, strCurrentFileExt, strNewFileExt)
                Application.DisplayAlerts = False
                Select Case strNewFileExt
                Case ".xlsx"
                    xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbook
                Case ".xlsm"
                    xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                End Select
                xlFile.Close
                Application.DisplayAlerts = True
            End If
        Next objFile
    ClearMemory:
        strCurrentFileExt = vbNullString
        strNewFileExt = vbNullString
        Set objFSO = Nothing
        Set objFolder = Nothing
        Set objFile = Nothing
        Set xlFile = Nothing
        strNewName = vbNullString
        strFolderPath = vbNullString
    End Sub
    
    

    이것은 XL 파일 형식에 대한 링크입니다 : https://msdn.microsoft.com/en-us/library/office/ff198017.aspx

    '-----------------------------------------

    비트 수정 : 이 코드를 확인하십시오. 확장명 만 변경했지만 호환성을 확인하십시오. 작동하고 있는지 알려주세요 ...

    Sub ChangeFileFormat_V1()
        Dim strCurrentFileExt   As String
        Dim strNewFileExt       As String
        Dim objFSO              As Object
        Dim objFolder           As Object
        Dim objFile             As File  'Object
        Dim xlFile              As Workbook
        Dim strNewName          As String
        Dim strFolderPath       As String
        strCurrentFileExt = ".xls"
        strNewFileExt = ".xlsx"
        strFolderPath = "C:\Users\Scorpio\Desktop\New folder"
        If Right(strFolderPath, 1) <> "\" Then
            strFolderPath = strFolderPath & "\"
        End If
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.getfolder(strFolderPath)
        For Each objFile In objFolder.Files
            strNewName = objFile.Name
            If Right(strNewName, Len(strCurrentFileExt)) = strCurrentFileExt Then
                strNewName = Replace(strNewName, strCurrentFileExt, strNewFileExt)
                Application.DisplayAlerts = False
                objFile.Name = strNewName
                Application.DisplayAlerts = True
            End If
        Next objFile
    ClearMemory:
        strCurrentFileExt = vbNullString
        strNewFileExt = vbNullString
        Set objFSO = Nothing
        Set objFolder = Nothing
        Set objFile = Nothing
        Set xlFile = Nothing
        strNewName = vbNullString
        strFolderPath = vbNullString
    End Sub
    
    

관련 자료

  • 이전 parsing - 자바 간단한 문장 파서
  • 다음 C 프로그램 MySQL 연결