Meta data of mp3 and wav files to Excel

[TimvMechelen]

Hi all,
Is it possible to get all meta data of mp3 and wav files into excel of a specific folder?
I need the data: filename, artists, song title, bpm, genre, album and key of the song.

Thank you in advance.

 

[Domenic]

In order to give you a more robust macro, a couple of questions...

1) Does the sheet containing that path to the source folder reside in the workbook running the code? Or will it reside in the active workbook?

2) Do you want the worksheet for the results added to the workbook running the code? Or the active workbook? Or in a newly created workbook?

 

[TimvMechelen]

In order to give you a more robust macro, a couple of questions...

1) Does the sheet containing that path to the source folder reside in the workbook running the code? Or will it reside in the active workbook?

2) Do you want the worksheet for the results added to the workbook running the code? Or the active workbook? Or in a newly created workbook?

1: what is the difference between the two?

2: I don't want to create a completely new workbook, just on another sheet in that workbook.

 

[Domenic]

1: what is the difference between the two?

Since the workbook containing the code may or may not be the active workbook, we need to know whether the code should always reference the active workbook or the code workbook.

 

[TimvMechelen]

Since the workbook containing the code may or may not be the active workbook, we need to know whether the code should always reference the active workbook or the code workbook.

The code should always reference to the workbook with the code.

 

[Domenic]

The following macro has been amended as follows...

1)  The path to the source folder is retrieved from Sheet1 within the code workbook, in cell A1.

2)  The path is tested to make sure that it's a valid one.

3)  Prior to adding to the code workbook a new worksheet for the results and naming it after the source folder, if a worksheet with the same name already exists, it is automatically deleted.

Here's the amended macro...

Option Explicit

Sub GetMetaDataFromSoundFiles()

    Dim objShellApp As Object
    Dim objFolder As Object
    Dim varColumns As Variant
    Dim arrData() As Variant
    Dim wksResults As Worksheet
    Dim strPath As String
    Dim strFilename As String
    Dim fileCount As Long
    Dim i As Long
    Dim j As Long
    
    strPath = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
    
    Set objShellApp = CreateObject("Shell.Application")
    
    On Error Resume Next
    Set objFolder = objShellApp.Namespace(CStr(strPath))
    If objFolder Is Nothing Then
        MsgBox "Folder not found!", vbExclamation, "Folder?"
        Set objShellApp = Nothing
        Exit Sub
    End If
    On Error GoTo 0
    
    varColumns = Array(166, 13, 21, 243, 16, 14, 247)
    
    ReDim arrData(0 To UBound(varColumns), 0 To objFolder.Items.Count)
    
    For i = LBound(arrData, 1) To UBound(arrData, 1)
        arrData(i, 0) = objFolder.GetDetailsOf(objFolder.Items, varColumns(i))
    Next i
    
    fileCount = 0
    For i = 0 To objFolder.Items.Count - 1
        strFilename = objFolder.GetDetailsOf(objFolder.Items.Item(CLng(i)), 0)
        If Right(strFilename, 4) = ".mp3" Or Right(strFilename, 4) = ".wav" Then
            fileCount = fileCount + 1
            For j = 0 To UBound(varColumns)
                arrData(j, fileCount) = objFolder.GetDetailsOf(objFolder.Items.Item(CLng(i)), varColumns(j))
            Next j
        End If
    Next i
    
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(objFolder.Title).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set wksResults = ThisWorkbook.Worksheets.Add
    wksResults.Name = objFolder.Title
    
    wksResults.Range("A1").Resize(UBound(arrData, 2) + 1, UBound(arrData, 1) + 1).Value = Application.Transpose(arrData)
    
    Set objShellApp = Nothing
    Set objFolder = Nothing
    Set wksResults = Nothing
    
End Sub

 

[csenor]

Good afternooon Domenic. I've copied your macro and tried to adjust it to look for photo metadata for filenames ".jpg" and ".jpeg". When I run the macro, I get the headers to the metadata that I'm searching for but I don't get any of the information for the 108 pictures in the file folder. I do not get any error messages.

Option Explicit


Sub GetMetaDataFromPictureFiles()


Dim objShellApp As Object
Dim objFolder As Object
Dim varColumns As Variant
Dim arrData() As Variant
Dim wksResults As Worksheet
Dim strPath As String
Dim strFilename As String
Dim fileCount As Long
Dim i As Long
Dim j As Long

strPath = ThisWorkbook.Worksheets("Sheet1").Range("B1").Value

Set objShellApp = CreateObject("Shell.Application")

On Error Resume Next
Set objFolder = objShellApp.Namespace(CStr(strPath))
If objFolder Is Nothing Then
MsgBox "Folder not found!", vbExclamation, "Folder?"
Set objShellApp = Nothing
Exit Sub
End If
On Error GoTo 0

varColumns = Array(0, 1, 2, 12, 30)

ReDim arrData(0 To UBound(varColumns), 0 To objFolder.Items.Count)

For i = LBound(arrData, 1) To UBound(arrData, 1)
arrData(i, 0) = objFolder.GetDetailsOf(objFolder.Items, varColumns(i))
Next i

fileCount = 0
For i = 0 To objFolder.Items.Count - 1
strFilename = objFolder.GetDetailsOf(objFolder.Items.Item(CLng(i)), 0)
If Right(strFilename, 4) = ".jpg" Or Right(strFilename, 4) = ".jpeg" Then
fileCount = fileCount + 1
For j = 0 To UBound(varColumns)
arrData(j, fileCount) = objFolder.GetDetailsOf(objFolder.Items.Item(CLng(i)), varColumns(j))
Next j
End If
Next i

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(objFolder.Title).Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set wksResults = ThisWorkbook.Worksheets.Add
wksResults.Name = objFolder.Title

wksResults.Range("A1").Resize(UBound(arrData, 2) + 1, UBound(arrData, 1) + 1).Value = Application.Transpose(arrData)

Set objShellApp = Nothing
Set objFolder = Nothing
Set wksResults = Nothing

End Sub

 

[Domenic]

I just tested it, and it seems to work fine. I entered the path to the folder in B1, which is located in Sheet1 within the workbook running the code, and it listed all of the JPG files, along with their related data. Here's a few things to double check...

1) Make sure that the path to your folder is correct.

2) Make sure that the folder in fact contains JPG or JPEG files.

3) Make sure that you in fact want to refer to B1 within the workbook running the code, instead of the active workbook or some other workbook.

 

[csenor]

I've copied the path directly from the address bar at the top of my file explorer and pasted that into my workbook running the code. The picture folder path contains several .jpeg pictures. I changed the macro back to looking at A1 for the path as well and still only get the column headers on the newly created worksheet. It grabs the correct name of the picture folder and makes that the tab name, but no information is listed.

It's the only excel document that I have open.

Does the file extension have to be a part of the visible name? My pictures are labeled as IMG_001

 

[Domenic]

So cell A1 is located in Sheet1, and in the workbook running the code?

Also, when you copy/paste the path from the address bar of file explorer, is it copying/pasting the complete?

 

[csenor]

I just experimented and made the file extension visible and added .JPG (case sensitive) to the code and it made the macro work. Thank you.

What language can I add to resize the columns and turn the data into an Excel table with alternating line colors?