Share via

batch convert xls to xlsx

Anonymous
2011-11-02T06:34:24+00:00

Hello,

Is there a tool that would allow me to batch convert 30 xls files within a single folder into xlsx files?

Cheers,

Mark

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-11-02T10:56:41+00:00

    This should work

    Sub ConvertToXlsx()

        Dim strPath As String

        Dim strFile As String

        Dim wbk As Workbook

        ' Path must end in trailing backslash

        strPath = "C:\Test"

        strFile = Dir(strPath & "*.xls")

        Do While strFile <> ""

            If Right(strFile, 3) = "xls" Then

                Set wbk = Workbooks.Open(Filename:=strPath & strFile)

                If wbk.HasVBProject Then

                  wbk.SaveAs Filename:=strPath & strFile & "m", _

                    FileFormat:=xlOpenXMLWorkbookMacroEnabled

                Else

                   wbk.SaveAs Filename:=strPath & strFile & "x", _

                    FileFormat:=xlOpenXMLWorkbook

                End If

                wbk.Close SaveChanges:=False

            End If

            strFile = Dir

        Loop

    End Sub

    60+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-11-02T07:22:17+00:00

    Here is a macro you could run:

    Sub ConvertToXlsx()

        Dim strPath As String

        Dim strFile As String

        Dim wbk As Workbook

        ' Path must end in trailing backslash

        strPath = "C:\Excel"

        strFile = Dir(strPath & "*.xls")

        Do While strFile <> ""

            If Right(strFile, 3) = "xls" Then

                Set wbk = Workbooks.Open(Filename:=strPath & strFile)

                wbk.SaveAs Filename:=strPath & strFile & "x", _

                    FileFormat:=xlOpenXMLWorkbook

                wbk.Close SaveChanges:=False

            End If

            strFile = Dir

        Loop

    End Sub

    Warning: .xlsx workbooks don't store macros, so any macros from the .xls workbooks will be lost.

    40+ people found this answer helpful.
    0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2014-10-28T20:46:13+00:00

    The code is intended to be run from within Excel. If you want to run it as a VBS file, it has to be modified:

    Set app = CreateObject("Excel.Application")

    Set fso = CreateObject("Scripting.FileSystemObject")

    strPath = "C:\Test"

    strPath = "C:\Users\gebruiker\Documents\Excel\Test"

    Set fld = fso.GetFolder(strPath)

    For Each fil In fld.Files

        If Right(fil.Name, 3) = "xls" Then

            Set wbk = app.Workbooks.Open(fil)

            If wbk.HasVBProject Then

                wbk.SaveAs fil & "m", 52

            Else

                wbk.SaveAs fil & "x", 51

            End If

            wbk.Close False

        End If

    Next

    app.Quit

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-04-22T17:32:46+00:00

    This worked superbly, even on a network location!

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-06-03T14:24:10+00:00

    Could you also do strPath recursive?

    Or is it possible to give in more locations?

    i have like 200+ locations which i need to convert :)

    2 people found this answer helpful.
    0 comments