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
Duzzy

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
noap

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
Duzzy

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
noap

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
Duzzy

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
noap

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
Duzzy

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
noap

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
strollin

W7 Ult desktop, W10 laptop, W10 laptop, W8.1 tablet
 
 

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
strollin

W7 Ult desktop, W10 laptop, W10 laptop, W8.1 tablet
 
 

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
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 21:05.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App