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

13 Apr 2012   #11

Windows 7 Ultimate x64
 
 

Sorry.
Quote:
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?

My System SpecsSystem Spec
.

13 Apr 2012   #12

Windows 7 Ultimate x64
 
 

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

Windows 7 Ultimate x64
 
 

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


13 Apr 2012   #14

Windows 7 Ultimate x64
 
 

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

Windows 7 Ultimate x64
 
 

First try this,
Code:
For /r "C:\Main Folder" %i in ("Specific Folder\*.xlsx") do echo %i >> FilePaths.txt
My System SpecsSystem Spec
13 Apr 2012   #16

Windows 7 Ultimate x64
 
 

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

Windows 7 Ultimate x64
 
 

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

Windows 7 Ultimate x64
 
 

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

Win 7 Ultimate (64-bit), Win 8 (64-bit)
 
 

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

Win 7 Ultimate (64-bit), Win 8 (64-bit)
 
 

Quote   Quote: Originally Posted by Duzzy View Post
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.
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 11:28 PM.
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