Windows 7: Batch processing (xlsx to xlsb) in Excel 2010

noap · 12 Apr 2012

Hello!

I need to convert xlsx-files to xlsb-files. 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.

My folder topology looks like this:
main folder -> containing 44 folders -> each of them containing 1-3 specific folders -> where each of those specific folders contain 1 specific folder -> which contains the xlsx-files (about 10-30 files) which need to be converted into xlsb-files.

In short: there are about 1800 xlsx-files widespread amongst some folders and subfolders. And because there are so many folders and files, it would be a horror to do this manually.

The format of the folders is the same for all of them (just timestamps and numbering differs).

Dwarf · 12 Apr 2012

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

noap · 12 Apr 2012

Convert XLS to XLSX

strollin · 12 Apr 2012

Quote: Originally Posted by noap

...
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.

noap · 12 Apr 2012

Yes exactly.
I'll try.

If someone else knows how to make this work, I'm willing to pay for it!

Dwarf · 12 Apr 2012

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

strollin · 12 Apr 2012

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.

noap · 13 Apr 2012

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.

Duzzy · 13 Apr 2012

Open a cmd window and paste this

Code:

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

Quote:

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

noap · 13 Apr 2012

But this returns me every folder with *.xlsx-files in it. That's not what I want. I can't filter for the specific xlsx-files by searching for *2010.xlsx for example, because the file names are different every time (different timestamps).

Windows 7: Batch processing (xlsx to xlsb) in Excel 2010

Batch processing (xlsx to xlsb) in Excel 2010 problems?