How to Quickly Export the Size Information of All Folders in a PST File to an Excel File

If you want to check the size information of each folder in a PST file, you can use the method exposed in this article. It’ll quickly export the size information of all folders in a PST file to an Excel file.

When your PST file is too large, you may wish to check the size of each folder. It’ll help you easily figure out which are the larger ones and do suitable archiving on basis of the size. In general, to check the folder size, you can simply right click on a folder and then choose “Properties”. In the new popup dialog box, you can click the “Folder Size” button. However, if you have created many folders, checking the size information one by one manually is considerably troublesome. So, if you want to batch get the size information of all folders in a PST file, you can use the following VBA code. It’ll export all the folder names and size information to an Excel file in one go.

Export the Size Information of All Folders in a PST File to an Excel File

1. To start with, launch your Outlook application.
2. Then switch to “Developer” tab and click the “Visual Basic” button or press “Alt + F11” keys to access VBA editor.
3. Next in the “Microsoft Visual Basic for Applications” window, open a module which is not in use.
4. Subsequently, copy and paste the following VBA codes into this module.

Dim strExcelFile As String
Dim objExcelApp As Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet

Sub ExportFodlerSizetoExcel()
    Dim objSourcePST As Outlook.Folder
    Dim objFolder As Outlook.Folder
 
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
    Set objExcelWorksheet = objExcelWorkbook.Sheets("Sheet1")
    objExcelWorksheet.Cells(1, 1) = "Folder"
    objExcelWorksheet.Cells(1, 2) = "Size"
 
    'Select a source PST file
    Set objSourcePST = Outlook.Application.Session.PickFolder

    For Each objFolder In objSourcePST.Folders
        Call ProcessFolders(objFolder)
    Next
 
    'Fit the columns from A to B
    objExcelWorksheet.Columns("A:B").AutoFit
 
    strExcelFile = "E:\Outlook\" & objSourcePST.Name & " Folder Size (" & Format(Now, "yyyy-mm-dd hh-mm-ss") & ").xlsx"
    objExcelWorkbook.Close True, strExcelFile

    MsgBox "Complete!", vbExclamation
End Sub

Sub ProcessFolders(ByVal objCurrentFolder As Outlook.Folder)
    Dim objItem As Object
    Dim lCurrentFolderSize As Long
    Dim nNextEmptyRow As Integer
 
    objCurrentFolder.Items.SetColumns ("Size")
    For Each objItem In objCurrentFolder.Items
        lCurrentFolderSize = lCurrentFolderSize + objItem.Size
    Next
 
    'Convert byte to kilobyte
    'To convert to megabyte, use:
    'lCurrentFolderSize = (lCurrentFolderSize / 1024) / 1024
    lCurrentFolderSize = lCurrentFolderSize / 1024
 
    nNextEmptyRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1

    'Add the values into the columns
    objExcelWorksheet.Range("A" & nNextEmptyRow) = objCurrentFolder.FolderPath
    objExcelWorksheet.Range("B" & nNextEmptyRow) = lCurrentFolderSize & " KB"
 
    If objCurrentFolder.Folders.Count > 0 Then
       For Each objSubfolder In objCurrentFolder.Folders
           Call ProcessFolders(objSubfolder)
       Next
    End If
End Sub

5. After that, you should set your Outlook macro security level to low.
6. Eventually, you can have a try.

• Press F5 key button in the current macro window.
• Then you will be requested to select a PST file, like the following image:
• After selecting a source file, Outlook will begin to export the size information to Excel.
• After macro finishes, you receive a message like the screenshot below.
• Lastly, you will be able to find the Excel file in the predefined local folder. The Excel file will look like the following:

Deal with Frequent PST Errors

Since Outlook is prone to errors, you may have ever suffered many issues in your Outlook. In order to better and more flexibly cope with frustrating PST troubles, it is advisable to keep an experienced and reputable fix utility in vicinity, such as DataNumen Outlook Repair. It is able to fix PST problems like a breeze.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server recovery and outlook repair software products. For more information visit www.datanumen.com