Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


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


12 Apr 2012   #1

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

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


My System SpecsSystem Spec
.

12 Apr 2012   #2

Windows 8.1 Pro RTM x64
 
 

What tools have you found? I'm having trouble locating any.
My System SpecsSystem Spec
12 Apr 2012   #3

Windows 7 Ultimate x64
 
 

My System SpecsSystem Spec
.


12 Apr 2012   #4

Win 7 Ultimate x64 desktop, Win 8.1.1 x64 laptop, Win 7 Home x64 netbook, Win 8.1.1 x64 tablet
 
 

Quote   Quote: Originally Posted by noap View Post
...
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.
My System SpecsSystem Spec
12 Apr 2012   #5

Windows 7 Ultimate x64
 
 

Yes exactly.
I'll try.

If someone else knows how to make this work, I'm willing to pay for it!
My System SpecsSystem Spec
12 Apr 2012   #6

Windows 8.1 Pro RTM x64
 
 

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


Attached Thumbnails
Batch processing (xlsx to xlsb) in Excel 2010-capture.png  
My System SpecsSystem Spec
12 Apr 2012   #7

Win 7 Ultimate x64 desktop, Win 8.1.1 x64 laptop, Win 7 Home x64 netbook, Win 8.1.1 x64 tablet
 
 

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.
My System SpecsSystem Spec
13 Apr 2012   #8

Windows 7 Ultimate x64
 
 

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.
My System SpecsSystem Spec
13 Apr 2012   #9

Windows 7 Ultimate x64
 
 

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
My System SpecsSystem Spec
13 Apr 2012   #10

Windows 7 Ultimate x64
 
 

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).
My System SpecsSystem Spec
Reply

 Batch processing (xlsx to xlsb) in Excel 2010




Thread Tools



Similar help and support threads for2: Batch processing (xlsx to xlsb) in Excel 2010
Thread Forum
Solved Excel 2013 crashes after opening and using any xls/xlsx file Microsoft Office
Excel 2010 Microsoft Office
Solved Excel 2010 help Microsoft Office
MS Office 2010 x64 trouble with .xlsx files Microsoft Office
excel 2010 Microsoft Office
Formatting Excel 2010, Excel 2003 Microsoft Office
Solved Excel 2010 Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 06:36 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33