What to do with weird excel files? (Excel macro to convert .xls files in a folder to .csv files)

I work with physiological data using Acqknowledge and I don't know what I did, but I exported my data in some weird Excel 2 workbook format (still uses the .xls extension for some reason). I couldn't even open these with R without hitting multiple errors, so I couldn't write any script there to open my 400 .xls files.

So I wrote/pasted chunks of other people's code to create this excel macro to convert the .xls files in a folder (includes subfolders, so you just need to select your master folder) to .csv. Upon running the macro, a prompt will come out to ask you to select a folder. Then it will save the files as .csv in the same place they were before.

Note that you need to enable Windows Script Host Object Model in your Visual Basic Editor (Tools > References > check Windows Script Host Object Model).

You can copy the following code into a macro or just download the .bas file down below and import the macro that way.

Enjoy!

----------------------------------

Sub CsvallSubfolders()

    Dim MyFolder As String 'Path collected from the folder picker dialog

    Dim MyFile As String 'Filename obtained by DIR function

    Dim wbk As Workbook 'Used to loop through each workbook

    Dim FSO As New FileSystemObject ' Requires "Windows Script Host Object Model" in Tools -> References

    Dim ParentFolder As Object, ChildFolder As Object

    On Error Resume Next

    Application.ScreenUpdating = False

    'Opens the folder picker dialog to allow user selection

    With Application.FileDialog(msoFileDialogFolderPicker)

        .Title = "Please select a folder"

        .Show

        .AllowMultiSelect = False

        If .SelectedItems.Count = 0 Then 'If no folder is selected, abort

            MsgBox "You did not select a folder"

            Exit Sub

        End If

        MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder

    End With

'Target File Extension (must include wildcard "*")

  myExtension = "*.xls*"

'Target Path with Ending Extention

  MyFile = Dir(MyFolder & myExtension)

    'Loop through all files in a folder until DIR cannot find anymore

    Do While MyFile <> ""

        'Opens the file and assigns to the wbk variable for future use

        Set wb = Workbooks.Open(Filename:=MyFolder & MyFile)

        'Ensure Workbook has opened before moving on to next line of code

      DoEvents

        sFileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".") - 1) & ".csv"

Application.DisplayAlerts = False

    '---> Now create a CSV of the active sheet

    ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlCSV

Application.DisplayAlerts = True

    

    'Save and Close Workbook

      wb.Close savechanges:=True

      

    'Ensure Workbook has closed before moving on to next line of code

      DoEvents

    'Get next file name

      MyFile = Dir

        

    Loop

    For Each ChildFolder In FSO.GetFolder(MyFolder).SubFolders

    'Target File Extension (must include wildcard "*")

         myExtension = "*.xls*"

        MyFile = Dir(MyFolder & ChildFolder.Name & "\" & myExtension) 'DIR gets the first file of the folder

        'Loop through all files in a folder until DIR cannot find anymore

        Do While MyFile <> ""

            'Opens the file and assigns to the wbk variable for future use

            Set wb = Workbooks.Open(Filename:=MyFolder & ChildFolder.Name & "\" & MyFile)

            'Replace the line below with the statements you would want your macro to perform

'Ensure Workbook has opened before moving on to next line of code

      DoEvents

          sFileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".") - 1) & ".csv"

Application.DisplayAlerts = False

    '---> Now create a CSV of the active sheet

    ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:=xlCSV

Application.DisplayAlerts = True

    

    'Save and Close Workbook

      wb.Close savechanges:=True

      

    'Ensure Workbook has closed before moving on to next line of code

      DoEvents

    'Get next file name

            MyFile = Dir 'DIR gets the next file in the folder

        Loop

    Next ChildFolder

'Message Box when tasks are completed

  MsgBox "Task Complete!"

ResetSettings:

  'Reset Macro Optimization Settings

    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub