Excel tutorials

9. Loop through all the sub folders and save all the workbooks without formulas

Continue tutorials from the previous web site. In this part of the tutorial, there is an example of VBA macros that can be used to archive all workbooks as values without formulas, in multiple sub-folders in one step.

The VBA macro below works the following

  • Passing loop through all folders and sub-folders
  • Opens every workbook
  • Go through all worksheets (select all data / copy / paste special / as values)
  • Closes and saves the workbook
  • Goes to the next workbook
  • After completing saving all files, the message 'Task Completed'

To give you a VBA macro function properly in the VBE to 'enable' Tools -> References -> Microsoft Scripting Runtime

Microsoft Scripting Runtime

Also, I would like to note that with this archiving action I had opted out of the "Ask to update automatic links" item. (Remember, I have links to other workbooks)

Ask to update automatic links

Save all Workbooks as Values Without Formulas in Top Folder and All Sub Folders

Copy this VBA macro to the newly opened Excel Workbook in standard Module1, modify the path and run it.

Option Explicit
Sub LoopThroughAllSubfolders()
'www.cpearson.com & HansV MVP
'published by www.ic-ims.com
Dim FSO As Scripting.FileSystemObject
Dim FF As Scripting.Folder

Set FSO = New Scripting.FileSystemObject
Set FF = FSO.GetFolder("D:\The records of road vehicles\Top Folder") 'defined path - change to suit
DoOneFolder FF

'Message Box when tasks are completed
MsgBox "Task Completed!"
End Sub
Sub DoOneFolder(FF As Scripting.Folder)
Dim F As Scripting.File
Dim SubF As Scripting.Folder
Dim WB As Workbook
Dim ws As Worksheet

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each F In FF.Files
If UCase(F.Name) Like "*.XLS*" Then 'open Excel files only
Set WB = Workbooks.Open(F.Path)
'Application.AskToUpdateLinks = False 'This prevented the Update Links message box from appearing when the file is opened
'Call 'your another VBA macro or Insert the actions to be performed in folder on each file
For Each ws In Worksheets
ws.UsedRange = ws.UsedRange.Value 'Save as Values without formulas all Sheet Tabs in Workbook
Next ws
WB.Close SaveChanges:=True
Debug.Print F.Name
End If
Next F

For Each SubF In FF.SubFolders
DoOneFolder SubF
Next SubF

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

After you start VBA macros, wait for a while. I applied this VBA macro to a 1201 Excel file and the save time was approximately 56 minutes. Top folder had a size of about 48 MB and after archiving without the formula had a size of about 36 MB.

This completes the bekap of all workbooks in all Sub Folders in the defined 'Top Folders'. And at the end of this tutorial that consists of several parts (web pages) I want to mention, this is a quick and easy-to-use example of work organization with a specific goal. Of course this can be done better and more professionally, but Excel is not a database (Acces and other software applications are used).
This is at the level of the average user of Excel who can improve the organization of work in some topic and within the scope of his job. Here are the key elements, month of the year, sumarum in the top folder, and missing sumarum files in each month (which I did not include here). If you are wondering why I like this imagined, reason is that your boss one day may request a report after months or years, and so on. However, everyone has their own style of work and organization. Of course, everything depends on the results you need in the basic template of your workbook (starting on the begin the complete tutorial) and what kind of formulas you need to set up there. If there is a lot of data, lots of rows, lots of Array formulas etc ... it can all make it harder to work and slow down Excel.

List all topics related to complete tutorials

  1. Creating Top Folders and Sub Folders in One Step
  2. How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
  3. Rename the first in the order of the worksheet, in multiple workbooks in the same folder
  4. How to remove the last Worksheet in multiple Workbooks in the same folder
  5. How to remove a particular VBA module in multiple workbooks in the same folder
  6. How to save all workbooks as values without formulas in all sub-folders

 I hope someone will help this complete tutorial. If there are mistakes, I apologize because I did not have enough time for a detailed review.