Intermediate
10 min read
Updated 26/10/2024
How to Export Metadata from Excel Files
Learn various methods to access, export, and analyze Excel file metadata, including built-in properties, custom properties, and advanced metadata extraction techniques.
In this tutorial:
- Accessing built-in file properties
- Extracting custom properties
- Using VBA for metadata export
- PowerShell methods
- Third-party tools and alternatives
You'll need:
- Excel (2010 or newer)
- Administrator access (for some methods)
- Basic VBA knowledge (optional)
How to Export Excel File Metadata
Method 1: Using Built-in Properties
Accessing File Properties
-
1
Open File Properties
- • Right-click Excel file → Properties
- • Or File → Info → Properties → Advanced Properties
-
2
Available Metadata Categories:
- • General: Size, Type, Location
- • Summary: Title, Subject, Author, Keywords
- • Statistics: Created, Modified, Accessed dates
- • Custom: User-defined properties
Method 2: VBA Metadata Export
Complete VBA Code Solution
Sub ExportMetadata()
Dim wb As Workbook
Dim ws As Worksheet
Dim prop As DocumentProperty
Dim row As Long
'Create new worksheet for metadata
Set wb = ThisWorkbook
Set ws = wb.Sheets.Add
ws.Name = "Metadata"
'Add headers
ws.Cells(1, 1) = "Property"
ws.Cells(1, 2) = "Value"
row = 2
'Built-in properties
With wb.BuiltinDocumentProperties
For Each prop In .Item
On Error Resume Next
ws.Cells(row, 1) = prop.Name
ws.Cells(row, 2) = prop.Value
row = row + 1
On Error GoTo 0
Next prop
End With
'Custom properties
With wb.CustomDocumentProperties
For Each prop In .Item
ws.Cells(row, 1) = prop.Name
ws.Cells(row, 2) = prop.Value
row = row + 1
Next prop
End With
'Format worksheet
ws.Columns("A:B").AutoFit
ws.Range("A1:B1").Font.Bold = True
End Sub
Method 3: PowerShell Solution
PowerShell Script
# Get Excel file metadata
$ExcelFile = Get-Item "Path\To\Your\File.xlsx"
$Shell = New-Object -ComObject Shell.Application
$Folder = $Shell.Namespace($ExcelFile.DirectoryName)
$File = $Folder.ParseName($ExcelFile.Name)
# Create empty array for metadata
$MetaData = @{}
# Loop through all available properties
0..400 | ForEach-Object {
$PropName = $Folder.GetDetailsOf($Folder.Items, $_)
$PropValue = $Folder.GetDetailsOf($File, $_)
if ($PropValue) {
$MetaData[$PropName] = $PropValue
}
}
# Export to CSV
$MetaData.GetEnumerator() |
Select-Object Name, Value |
Export-Csv -Path "Metadata.csv" -NoTypeInformation
Method 4: Using Windows Explorer
-
1
Open File Location
Navigate to the folder containing your Excel file
-
2
Change View Settings
View → Details
-
3
Add Metadata Columns
Right-click column header → More... → Select desired properties
-
4
Export to Excel
Select all → Copy → Paste into Excel
Best Practices and Tips
Do's:
- Regularly update file properties
- Use consistent naming conventions
- Back up metadata separately
Don'ts:
- Store sensitive info in metadata
- Ignore custom properties
- Forget to validate exported data
Troubleshooting Common Issues
-
Missing Properties:
If properties are not visible:
- • Check file permissions
- • Ensure file isn't marked as read-only
- • Try opening file in Protected View
-
VBA Runtime Errors:
'Add error handling On Error Resume Next If Err.Number <> 0 Then Debug.Print "Error " & Err.Number & ": " & Err.Description End If On Error GoTo 0
Advanced Metadata Management
Batch Processing Multiple Files
Sub ExportMultipleFilesMetadata()
Dim FolderPath As String
Dim FileName As String
Dim wb As Workbook
Dim ws As Worksheet
'Select folder with Excel files
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
FolderPath = .SelectedItems(1)
End With
'Create summary worksheet
Set wb = Workbooks.Add
Set ws = wb.Sheets(1)
'Process each Excel file
FileName = Dir(FolderPath & "\*.xls*")
Do While FileName <> ""
'Extract metadata for each file
'Add to summary sheet
FileName = Dir
Loop
End Sub
Creating Custom Metadata Interface
Sub CreateMetadataUserForm()
'Add UserForm code here
With UserForm1
.StartUpPosition = 0
.Left = Application.Left + 100
.Top = Application.Top + 100
.Show
End With
End Sub
Integration with Other Tools
SharePoint Integration
- Use SharePoint columns for metadata
- Sync metadata with SharePoint properties
- Use Power Automate for automation
Power BI Connection
- Create metadata dashboards
- Track file usage patterns
- Monitor metadata changes
Security Considerations
-
Privacy Concerns:
- • Remove personal information before sharing
- • Check for hidden metadata
- • Use Document Inspector before distribution
-
Compliance:
- • Follow data protection regulations
- • Document metadata handling procedures
- • Maintain audit trails of changes
Regular Maintenance Checklist
-
Weekly Tasks:
- • Update modified dates
- • Check for missing properties
- • Verify metadata accuracy
-
Monthly Tasks:
- • Full metadata backup
- • Clean up obsolete properties
- • Update automation scripts