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
noap

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
Dwarf

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
noap

Windows 7 Ultimate x64
 
 

My System SpecsSystem Spec
.

12 Apr 2012   #4
strollin

W10 Pro desktop, W10 laptop, W10 laptop, W10 Pro tablet (all 64-bit)
 
 

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
noap

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
Dwarf

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
-capture.png  
My System SpecsSystem Spec
12 Apr 2012   #7
strollin

W10 Pro desktop, W10 laptop, W10 laptop, W10 Pro tablet (all 64-bit)
 
 

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
noap

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
Duzzy

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
noap

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
Thread Forum
Excel connot open file (filename) xlsx
I have several excel files stored on my system. I used these files from the same computer daily. Yesterday, when i tried to open several files in Excel 2007 then i found following error on my co,puter screen: "Excel connot open file (filename) xlsx because the file format or file extension is not...
Microsoft Office
Excel 2013 crashes after opening and using any xls/xlsx file
Hi Members, when I open any XLS or XLSX file after few seconds or after doing any activity in any xls/xlsx file, Excel 2013 crashes (Office 2013 is updated to latest). There are no third party plugins enabled in Office 2013. http://i.imgur.com/LRSV58D.png http://i.imgur.com/nO793Dg.png ...
Microsoft Office
Excel 2013 - Processor overheats when processing large files
Hello, I have a very rare issue here. I'm trying to convert a normal xlsx file into a csv file. In the xlsx file there are normal columns and rows, nothing strange, nor formatting or something pretty, only colums and rows with products plain information. I've received these files via...
Microsoft Office
How to go back from Excel 2010 64 bit to Excel 2010 32 bit
I have an important program that uses a DLL that only works with Excel 2010 32 bit. I'm running Excel 2010 64 bit now -- how do I install the 32 bit version? I have the CD which has both versions on it. But when I run Setup, it gives me options that I don't understand for this limited purpose. In...
Microsoft Office
MS Office 2010 x64 trouble with .xlsx files
I've exhausted every measure and I cannot figure this out. One of the computers at my work is running Windows 7 x64 and I've installed Microsoft Office 2010 x64. Everything was fine at first, everything ran the way it was supposed to. Then I get a call from an employee saying that when he...
Microsoft Office
Formatting Excel 2010, Excel 2003
Hello, I want to create a table as in the drawing below, where the first cell is table-wide (as it contains the heading common (pertaining?) to all columns and rows under it) but, and this is where I cannot get it together, where those cells are divided into several columns. It's a cinch when...
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 04:15.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App