Solved Batch processing (xlsx to xlsb) in Excel 2010

noap · Apr 12, 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 · Apr 12, 2012

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

noap · Apr 12, 2012

Convert XLS to XLSX

strollin · Apr 12, 2012

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.

noap · Apr 12, 2012

Yes exactly.
I'll try.

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

Dwarf · Apr 12, 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 · Apr 12, 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 · Apr 13, 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 · Apr 13, 2012

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

noap · Apr 13, 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).

Duzzy · Apr 13, 2012

Sorry.

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.

So the 44 subfolders contain around 1800 xlsx files?
Is the "1 specific folder" in the subfolder named the same?
Is that 10-30 files in each "1 specific folder" or all up?
Do the files that need converting have anything in common. eg tag, dates?
Can you do a search in Explorer and return those specfic files or sort returned files so there grouped?

noap · Apr 13, 2012

Yes, the "1 specific folder" you bolded is named exactly the same.
And yes, this "1 specific folder" contains 10-30 files.
Well, the files have something in common, but there are files named the same in other folders, which I don't want to be converted.
So no, I can't (at least I can't think of a way how I could) search for the specific files only.

Duzzy · Apr 13, 2012

If there's only 10-30 files in one folder that need converting it wil probably be easier just to copy the paths one by one.

Otherwise does the "1 specific folder" named exactly the same exist in multiple subfolders and contain only the files to be converted?

Draw me a better picture, eg.
Main Folder
-Sub1
---FolderW
-----Files (don't convert)
---FolderX
-----Files (don't convert)
---Specific Folder
-----Files (All Convert)

-Sub2
---FolderY
-----Files (don't convert)
---FolderZ
-----Files (don't convert)
---Specific Folder
-----Files (Some Convert)

noap · Apr 13, 2012

I don't think so. Like I said, altogether there are like 1800 xlsx files. The "1 specific folder" contains 10-30 files. So I have about 90 of those "1 specific folders". Therefore copying the paths one-by-one is too time consuming.

Yes Exactly. The "1 specific folder" has the same name in every of the subfolders. This "1 specific folder" contains only files which need to be converted (10-30 files).
So if it's possible to search for all of the folders named like the "1 specific folder" and to convert all the xlsx files it has in it, that should do the trick.

*edit*
just saw your edit after posting. I'm going to prepare a better picture for you. just one sec...

Duzzy · Apr 13, 2012

First try this,

Code:

For /r "[B]C:\Main Folder[/B]" %i in ("[B]Specific Folder[/B]\*.xlsx") do echo %i >> FilePaths.txt

noap · Apr 13, 2012

^works, BUT returns the path of each xlsx file. What I need for the macro strollin posted, is the path of the "specific folders" only.

Duzzy · Apr 13, 2012

My mistake again.
Just open the main folder in Explorer and search for "Specific Folder".
Select all folders, hold shift and right click.
Click Copy as Path
Paste into notepad

Edit > Replace
Find: "
Leave Replace blank and Replace all

Edit > Replace
Find: Specific Folder
Replace: Specific Folder\
Replace all

I tested the macro and it won't work if the path contains the quotes.

Edit: Just paste straight into Excel and do the second Replace

noap · Apr 13, 2012

ah right, I could have thought of that myself. :\
Thanks a lot!
Rep added to both of you, Duzzy and strollin!

(I'll run the macro while I'm away, I'll report back if everything went fine)

strollin · Apr 13, 2012

I don't think anyone can provide you with a 1-click solution, whatever method you use will require you to put in some manual effort.

strollin · Apr 13, 2012

Duzzy said:
My mistake again.
Just open the main folder in Explorer and search for "Specific Folder".
Select all folders, hold shift and right click.
Click Copy as Path
Paste into notepad

Edit > Replace
Find: "
Leave Replace blank and Replace all

Edit > Replace
Find: Specific Folder
Replace: Specific Folder\
Replace all

I tested the macro and it won't work if the path contains the quotes.

Edit: Just paste straight into Excel and do the second Replace

The macro could be modified to add the backslash which would eliminate the need for the second Replace. Replace this line:

Code:

strPath = ThisWorkbook.Worksheets(1).Cells(i, 1).Value

with this:

Code:

strPath = ThisWorkbook.Worksheets(1).Cells(i, 1).Value & "\"

Either way, all the paths in the list need to be consistent.

Solved Batch processing (xlsx to xlsb) in Excel 2010

New member

My Computer My Computer

At a glance

The Contemplator, (1963-2013)

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

Old Guys Rule!

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

The Contemplator, (1963-2013)

Attachments

My Computer My Computer

At a glance

Old Guys Rule!

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

New member

My Computer My Computer

At a glance

Old Guys Rule!

My Computer My Computer

At a glance

Old Guys Rule!

My Computer My Computer

At a glance

Similar threads

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer

My Computer