Batch processing (xlsx to xlsb) in Excel 2010

What tools have you found? I'm having trouble locating any.

noap said:

...
I've found some tools on the internet, which are able to do that, but only if all the xlsx-files are in one folder. ...

That tool you linked to can handle multiple folders for the xls files but all of the converted files are placed in the same output folder. I didn't see any option to output the files to their original folder.

I assume you want the converted files to be outputted to their original folder?

It should be a trivial task for the developer to include the option to output the converted file to their original folder. You could try to contact the developer and ask if he would consider adding that option.

Here's one I found that can do command line processing: http://www.coolutils.com/Convert-XLS-XLSX With command line ability, you could write a batch file that changes to each folder that contains files you need to convert, do the conversion, then change to the next folder. It would take some time to write the batch file but it should do what you want. Oops! Just realized that this tool doesn't support converting to xlsb, only converts to xlxs so it won't do the job.

I have located a program that will do what you want, with the ability to convert files in different folders, as you have stored them. Unfortunately, it is not free and is currently $159.95 according to the website. I'm not sure of your budget, but if you want to have a look the link is below. Note that unregistered versions will only convert the first 2 files found at a time.

Conversion from excel xls Database Spreadsheets to pdf, images, XLS, HTML, XML quickly

Here's a macro that should do what you want:

Code:

Sub ConvertToXlsb()
   Dim strPath As String
   Dim strFile, strConvFile As String
   Dim wbk As Workbook
   Dim i As Integer
   Dim Dun As Boolean
   
   i = 0
   Dun = False
   
   Do Until Dun
      i = i + 1
      If ThisWorkbook.Worksheets(1).Cells(i, 1) <> "" Then
         ' Path must end in trailing backslash
         ' IE C:\Test\
         strPath = ThisWorkbook.Worksheets(1).Cells(i, 1).Value
         strFile = Dir(strPath & "*.xlsx")
         Do While strFile <> ""
            If Right(strFile, 4) = "xlsx" Then
               Set wbk = Workbooks.Open(Filename:=strPath & strFile)
               strConvFile = Replace(strFile, "xlsx", "xlsb")
               wbk.SaveAs Filename:=strPath & strConvFile, FileFormat:=xlExcel12
               wbk.Close SaveChanges:=False
            End If
            strFile = Dir
         Loop
      Else
         Dun = True
      End If
   Loop
End Sub

If you put the list of folders in column A of a worksheet (note that the macro expects the folder spec to end in a backslash), it will loop thru each folder in the list and convert all .xls files within the folder to .xlsb. The original file will remain unchanged and the new file will be saved in the original folder.

wow, thanks a lot!

just one question if I got that right:
Do I have to enter every folder with the xlsx-files? Because there are approximately 100 folders. Copying every folderpath to excel worksheet can be a bit timeconsuming.

Open a cmd window and paste this

Code:

dir /b /on /s "C:\Folder"\*.xlsx > FilePaths.txt

If you put the list of folders in column A of a worksheet (note that the macro expects the folder spec to end in a backslash), it will loop thru each folder in the list and convert all .xls files within the folder to .xlsb.

Now open FilePaths.txt in notepad.
Select Edit > Replace.
Find = .xlsx
Replace = .xlsx\
Replace all
Select all and copy to Column A