When working in a team, we often need to know beyond just numbers. You also need to know the internal details, like who created it and the custom notes behind each. This information is often known as the metadata and is sometimes more important than the raw ones. Just imagine how you would know when the author did the last edit. The guess game is not gonna make reporting serious. The best way to know that is by exporting the metadata.
To export the metadata of an Excel file, go through these steps –
➤ Open Excel and create a blank workbook.
➤ Go to Data tab -> Get Data.
➤ From the Get Data option, select the From File option.
➤ Under the From File menu, choose From Folder.
➤ Select the folder from the location you want.
➤ This will open the file names with the last modified time and other relevant info into a new virtual window.
➤ Click on Load to load the data into the worksheet
That’s only scratching the surface. If you really want to learn how to export metadata of an Excel file, there are myriad options for you. Starting from the Get Data and manual entry, there are other automated solutions for VBA, Power Query, and PowerShell. Whether you need to check the author’s name or last name, or need full metadata of the dozens of spreadsheets, you can tailor any method to your needs. So, let’s lift the curtain as we walk into the inside-out of each step.
Extract Metadata Manually from an Excel File
In cases when you only need the metadata of one file and do not want any automation, it is best to extract metadata manually. The automation is best for bulk metadata. Excel has the built-in Info option, which enables you to view the information of the file in an instant. It is perfect for quick check-ins and keeping track of important files.
Steps:
➤ Open the file whose metadata you want to see.
➤ Go to the File -> Info.
➤ It will view all the file metadata, including Author, Last modified, Created, Size, and other information.
➤ To view more information, click on Show All Properties.
➤ Now, you can copy this information and save it anywhere you want for future use.
Notes:
➨ You can also edit some of the metadata directly by clicking on them.
➨ As you manually copy or save them somewhere, it is not refreshable. You need to copy every time a new change is applied.
Use Excel’s Get Data Feature to Extract File Metadata
If you need to export details of multiple files, you can’t manually do that for every file. In that case, the fastest, no-code, and most suitable option is to get the metadata through the Get Data feature. It is a simple and built-in Excel option. It shows the file types, locations, creation, and last modified dates along with other information. Even better is the fact that it can be refreshed every time new files are added.
We will find the metadata of the files in the Customer Satisfaction Report folder. It contains four files below-
Steps:
➤ Open Excel, create a blank workbook, and go to the Data tab.
➤ In the Data tab, click on the Get Data Option.
➤ Down the menu of the Get Data, select From File -> From Folder.
➤ This will open the folder section of your device to choose from. Select the folder from which you want to extract the metadata.
➤ It will open a virtual window with the metadata of all the files in the folder and the relevant hidden information like location, last modified data, etc.
➤ Click on Load to load the information into the worksheet.
➤ Add relevant formatting styles as you require after loading the data into the worksheet.
Notes:
➨ It is suitable for all file types.
➨ Only collects file system metadata, not document-specific information like Author.
Integrate Power Query with the Get Data Option to Advance Control
As you know, the Get Data option is sufficient to get the relevant information from the files you want to extract the metadata from. But sometimes, these are not enough. For advanced control, you may need to edit the information present in the metadata. This is also not a hassle as long as you can integrate Power Query automation.
Steps:
➤ Open an Excel file and go to the Data tab -> Get Data.
➤ In the Get Data option, select From File -> From Folder.
➤ It will launch the File Explorer to select the folder that you want to open.
➤ Clicking on it opens a virtual window with the datasheet of the metadata present for all the files.
➤To edit, click on the Transform button.
➤ This will launch the Power Query Editor window with the metadata sheet.
➤ From here, you can add or delete your desired columns or rows. For example, we will delete columns like Content, Folder Path, and Attributes that are irrelevant to us.
➤ When done, click on Close and Load under the Home tab.
➤ This will load the information to a new worksheet. Refresh it anytime to update the information with the latest metadata.
Notes:
➨ Can not extract metadata from internal systems, such as author and title.
➨ To add a new column, go to the Add Column tab -> Custom Column. Set it a new name, and a formula if needed.
➨ To delete the column, right-click the column and select Remove.
Extract Internal Document Properties with VBA
As you can see, Power Query is a helpful tool for getting the metadata for the required file. The problem is that it only works on the surface level. It can not read the internal document properties, like Author, Title, and Subject. The automation of VBA bridges this gap. All you need is a simple, short macro that will loop through the workbooks and pull in-built custom properties directly into an Excel document.
Steps:
➤ Open the file and go to the Developer tab -> Visual Basic.
➤ This will open the VBA editor window. Click the Insert tab and select Module.
➤ Paste the following VBA code into the blank space –
Sub ExportAllMetadata()
Dim ws As Worksheet
Dim fso As Object, folder As Object, file As Object
Dim i As Long
Dim filePath As String
' CHANGE THIS
filePath = "C:\Users\User\Documents\Customer Satisfaction Report\"
' Setup output
Set ws = ThisWorkbook.Sheets("MetadataOutput")
ws.Cells.Clear
ws.Range("A1:F1").Value = Array("File Name", "File Type", "Author", "Title", "Last Saved By", "Created Date")
' File system
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(filePath)
i = 2
For Each file In folder.Files
Dim ext As String
ext = LCase(fso.GetExtensionName(file.Path))
Select Case ext
Case "xlsx", "xlsm", "xls"
' Excel
Dim wb As Workbook
Set wb = Workbooks.Open(file.Path, False, True)
ws.Cells(i, 1).Value = file.Name
ws.Cells(i, 2).Value = "Excel"
On Error Resume Next
ws.Cells(i, 3).Value = wb.BuiltinDocumentProperties("Author")
ws.Cells(i, 4).Value = wb.BuiltinDocumentProperties("Title")
ws.Cells(i, 5).Value = wb.BuiltinDocumentProperties("Last Author")
ws.Cells(i, 6).Value = wb.BuiltinDocumentProperties("Creation Date")
On Error GoTo 0
wb.Close False
Case "docx", "doc"
' Word
Dim wdApp As Object, wdDoc As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open(file.Path, ReadOnly:=True)
ws.Cells(i, 1).Value = file.Name
ws.Cells(i, 2).Value = "Word"
On Error Resume Next
ws.Cells(i, 3).Value = wdDoc.BuiltinDocumentProperties("Author")
ws.Cells(i, 4).Value = wdDoc.BuiltinDocumentProperties("Title")
ws.Cells(i, 5).Value = wdDoc.BuiltinDocumentProperties("Last Author")
ws.Cells(i, 6).Value = wdDoc.BuiltinDocumentProperties("Creation Date")
On Error GoTo 0
wdDoc.Close False
wdApp.Quit
Case "pptx", "ppt"
' PowerPoint
Dim ppApp As Object, ppPres As Object
Set ppApp = CreateObject("PowerPoint.Application")
Set ppPres = ppApp.Presentations.Open(file.Path, , , msoTrue)
ws.Cells(i, 1).Value = file.Name
ws.Cells(i, 2).Value = "PowerPoint"
On Error Resume Next
ws.Cells(i, 3).Value = ppPres.BuiltinDocumentProperties("Author")
ws.Cells(i, 4).Value = ppPres.BuiltinDocumentProperties("Title")
ws.Cells(i, 5).Value = ppPres.BuiltinDocumentProperties("Last Author")
ws.Cells(i, 6).Value = ppPres.BuiltinDocumentProperties("Creation Date")
On Error GoTo 0
ppPres.Close
ppApp.Quit
Case "txt"
' Text file ? only file system metadata
ws.Cells(i, 1).Value = file.Name
ws.Cells(i, 2).Value = "Text"
ws.Cells(i, 3).Value = "" ' No author
ws.Cells(i, 4).Value = "" ' No title
ws.Cells(i, 5).Value = "" ' No last author
ws.Cells(i, 6).Value = file.DateCreated
Case Else
' Other file types
ws.Cells(i, 1).Value = file.Name
ws.Cells(i, 2).Value = "Other"
ws.Cells(i, 3).Value = ""
ws.Cells(i, 4).Value = ""
ws.Cells(i, 5).Value = ""
ws.Cells(i, 6).Value = file.DateCreated
End Select
i = i + 1
Next file
MsgBox "Export Done. " & (i - 2) & " files processed."
End Sub
➤ Save the VBA code and close the window.
➤ In your Excel sheet, go to Developer tab -> Macros
➤ In the Macros window, select the function created by VBA Macros (ExportAllMetadata) and click Run.
➤ This will automatically fill the new sheet with the file-level information coded in VBA for each file type.
➤ Not all the data are properly formatted by VBA. For example, the dates (Created Date) must be formatted as Date. Select the column and press Ctrl + 1 to choose the desired format.
➤ The blank cells indicate no metadata is found for those terms.
Notes:
➨ Replace the folder path of line 7 with your folder location –
filePath = “C:\Users\User\Documents\Customer Satisfaction Report\”
➨ Change the sheet name in parentheses to your sheet name in line 9.
Set ws = ThisWorkbook.Sheets(“MetadataOutput”)
➨ This VBA code sets the headers itself and finds the file metadata like Author, Title, Last Saved By, and Created Date. You can include more if you want.
➨ The VBA only exports the data into the worksheet with a proper header. It does not do any formatting. Format and style your cells so that they resemble your dataset and goals.
Use PowerShell to Export File Metadata into a New Sheet
If you require more specific information than just Author, Title, or Created Date, VBA or Power Query will not be feasible. For more vivid information like Words, Counts, and Tags, PowerShell is the best alternative. It has the capability to access the Details tab of the file and pull all information from each file top-to-bottom. No wonder it is the best for professional use.
Steps:
➤ Open the Notepad and write the following script –
$FolderPath = "C:\Users\User\Documents\Customer Satisfaction Report"
$OutputFile = "C:\Users\User\Documents\Customer Satisfaction Report\MetadataExport.csv"
$Shell = New-Object -ComObject Shell.Application
$Folder = $Shell.Namespace($FolderPath)
$Data = @()
foreach ($File in $Folder.Items()) {
$Props = @{}
for ($i = 0; $i -lt 266; $i++) { # 266 covers all possible property indexes
$Name = $Folder.GetDetailsOf($Folder.Items, $i)
if ($Name -and $Name.Trim() -ne "") {
$Value = $Folder.GetDetailsOf($File, $i)
$Props[$Name] = $Value
}
}
$Data += New-Object PSObject -Property $Props
}
$Data | Export-Csv -Path $OutputFile -NoTypeInformation -Encoding UTF8
Write-Output "Metadata export complete. File saved to $OutputFile"
➤ Save the file with a .ps1 extension (not .txt). For example, you can save it as ExportMetaData.ps1.
➤ Now, open PowerShell and run it as Administrator.
➤ In PowerShell, write the file path of the .ps1 file you created (e.g., ExportMetaData.ps1)-
C:\Users\User\Desktop\ExportMetaData.ps1
This will export all the metadata of the files in a new MetadataExport.csv file.
➤ In the MetadataExport file, you will find all the detailed metadata along with the headers.
(You can see all the other relevant information in the file by downloading our workbooks.)
Notes:
If giving the File path in PowerShell does not work and shows a restricted message, run this command.
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
Then press Y for yes. And try the command again.
Frequently Asked Questions (FAQs)
Which metadata fields are safe to export?
Most of the file system metadata is safe to export. Even the document-specific ones are also okay as long as they do not contain sensitive information. However, you need to be careful about sharing those files externally, as they can reveal any sensitive details about the users and the organizations.
Can I export custom document properties?
Custom Document Properties can be created in both Excel, Word, and PowerPoint. These properties can be accessed using customized VBA functions and PowerShell. However, with Power Query, you can’t access that document-specific information.
Is there metadata that gets lost when the file is saved again?
Some of the metadata is overwritten when the file is resaved. For example, the fields like Last Modified or Last Author are frequently changed when the file is saved or edited. To preserve this information, keep a backup of the original one..
How do you export metadata for many files automatically?
To export metadata for bulk files automatically, you can use either Power Query, VBA, or PowerShell. However, VBA can lag performance for different file types, and the script gets unnecessarily larger. As a result, Powery Query and PowerShell can capture the extended properties faster and easier.
Can I include hidden metadata like author and last saved by without VBA or PowerShell?
No, not fully. Not using VBA or PowerShell means you are just relying on Power Query. Unfortunately, it does not allow you to go beyond file system properties, and the hidden metadata, like Author and Last Saved By, can not be accessed.
Concluding Words
Exporting metadata is not a hard task. From simple manual entry for single files, there are also methods for extracting metadata of multiple files and types. For system-level details, Power Query is the best approach. However, the suitable options for advanced control and more internal document-specific information are VBA Macros and PowerShell.
Leaning all these methods together, you get a complete toolkit from quick checks to enterprise-level bulk reporting. Whether you are an analyst, an IT admin, or an avid enthusiast, don’t hesitate to go through all of them, download our workbooks, and pick a method that fits your workflow.
