Meta data of mp3 and wav files to Excel

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
120
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,298
The following macro assumes that the specified folder exists, and that a workbook is active. It creates a new worksheet within the active workbook, and places the results in that worksheet starting at cell A1. Here's the code...

Code:
Option Explicit

Sub GetMetaDataFromSoundFiles()


    Dim objShellApp As Object
    Dim objFolder As Object
    Dim varColumns As Variant
    Dim arrData() As Variant
    Dim strFilename As String
    Dim fileCount As Long
    Dim i As Long
    Dim j As Long
    
    Set objShellApp = CreateObject("Shell.Application")
    Set objFolder = objShellApp.Namespace("C:\Users\Domenic\Documents\Sounds") 'change the path to the source folder accordingly
    
    varColumns = Array(0, 20, 21, 28, 16, 14)
    
    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
    
    Worksheets.Add
    
    Range("A1").Resize(UBound(arrData, 2) + 1, UBound(arrData, 1) + 1).Value = Application.Transpose(arrData)
    
End Sub

Note that since I did not understand what you meant by "key of the song", it's not included in the result.

Hope this helps!
 

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
120
Thank you Domenic! When I run the macro I get: the name, contributing artists, title, bit rate, Genre and Album.
Do you know if there is a possibility to get the "Initial key" and the "Beats-per-minute" in Excel too (as you can see below)?


Again, Thanks a lot for your help!
 

Repush

Board Regular
Joined
Sep 21, 2015
Messages
133
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

based on the link you should add 221 (key) and 219 (BPM) to the varColumns-Array
Code:
varColumns = Array(0, 20, 21, 28, 16, 14, 221, 219)
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,298
I've taken a look at that link, but found that the Shell property file values for Beats-per-minute and Initial key should actually be 243 and 247, respectively. Also, I've re-read your original post. Accordingly, try the following instead...

Code:
varColumns = Array(166, 13, 21, 243, 16, 14, 247)

The result should be in the following format...

FilenameContributing artistsTitleBeats-per-minuteGenreAlbumInitial key

<tbody>
</tbody>

Here is the list of Shell file properties that I get...

0Name
1Size
2Item type
3Date modified
4Date created
5Date accessed
6Attributes
7Offline status
8Availability
9Perceived type
10Owner
11Kind
12Date taken
13Contributing artists
14Album
15Year
16Genre
17Conductors
18Tags
19Rating
20Authors
21Title
22Subject
23Categories
24Comments
25Copyright
26#
27Length
28Bit rate
29Protected
30Camera model
31Dimensions
32Camera maker
33Company
34File description
35Masters keywords
36Masters keywords
37
38
39
40
41
42
43Program name
44Duration
45Is online
46Is recurring
47Location
48Optional attendee addresses
49Optional attendees
50Organizer address
51Organizer name
52Reminder time
53Required attendee addresses
54Required attendees
55Resources
56Meeting status
57Free/busy status
58Total size
59Account name
60
61Task status
62Computer
63Anniversary
64Assistant's name
65Assistant's phone
66Birthday
67Business address
68Business city
69Business country/region
70Business P.O. box
71Business postal code
72Business state or province
73Business street
74Business fax
75Business home page
76Business phone
77Callback number
78Car phone
79Children
80Company main phone
81Department
82E-mail address
83E-mail2
84E-mail3
85E-mail list
86E-mail display name
87File as
88First name
89Full name
90Gender
91Given name
92Hobbies
93Home address
94Home city
95Home country/region
96Home P.O. box
97Home postal code
98Home state or province
99Home street
100Home fax
101Home phone
102IM addresses
103Initials
104Job title
105Label
106Last name
107Mailing address
108Middle name
109Cell phone
110Nickname
111Office location
112Other address
113Other city
114Other country/region
115Other P.O. box
116Other postal code
117Other state or province
118Other street
119Pager
120Personal title
121City
122Country/region
123P.O. box
124Postal code
125State or province
126Street
127Primary e-mail
128Primary phone
129Profession
130Spouse/Partner
131Suffix
132TTY/TTD phone
133Telex
134Webpage
135Content status
136Content type
137Date acquired
138Date archived
139Date completed
140Device category
141Connected
142Discovery method
143Friendly name
144Local computer
145Manufacturer
146Model
147Paired
148Classification
149Status
150Status
151Client ID
152Contributors
153Content created
154Last printed
155Date last saved
156Division
157Document ID
158Pages
159Slides
160Total editing time
161Word count
162Due date
163End date
164File count
165File extension
166Filename
167File version
168Flag color
169Flag status
170Space free
171
172
173Group
174Sharing type
175Bit depth
176Horizontal resolution
177Width
178Vertical resolution
179Height
180Importance
181Is attachment
182Is deleted
183Encryption status
184Has flag
185Is completed
186Incomplete
187Read status
188Shared
189Creators
190Date
191Folder name
192Folder path
193Folder
194Participants
195Path
196By location
197Type
198Contact names
199Entry type
200Language
201Date visited
202Description
203Link status
204Link target
205URL
206
207
208
209Media created
210Date released
211Encoded by
212Episode number
213Producers
214Publisher
215Season number
216Subtitle
217User web URL
218Writers
219
220Attachments
221Bcc addresses
222Bcc
223Cc addresses
224Cc
225Conversation ID
226Date received
227Date sent
228From addresses
229From
230Has attachments
231Sender address
232Sender name
233Store
234To addresses
235To do title
236To
237Mileage
238Album artist
239Sort album artist
240Album ID
241Sort album
242Sort contributing artists
243Beats-per-minute
244Composers
245Sort composer
246Disc
247Initial key
248Part of a compilation
249Mood
250Part of set
251Period
252Color
253Parental rating
254Parental rating reason
255Space used
256EXIF version
257Event
258Exposure bias
259Exposure program
260Exposure time
261F-stop
262Flash mode
263Focal length
26435mm focal length
265ISO speed
266Lens maker
267Lens model
268Light source
269Max aperture
270Metering mode
271Orientation
272People
273Program mode
274Saturation
275Subject distance
276White balance
277Priority
278Project
279Channel number
280Episode name
281Closed captioning
282Rerun
283SAP
284Broadcast date
285Program description
286Recording time
287Station call sign
288Station name
289Summary
290Snippets
291Auto summary
292Relevance
293File ownership
294Sensitivity
295Shared with
296Sharing status
297
298Product name
299Product version
300Support link
301Source
302Start date
303Sharing
304Availability status
305Status
306Billing information
307Complete
308Task owner
309Sort title
310Total file size
311Legal trademarks
312Video compression
313Directors
314Data rate
315Frame height
316Frame rate
317Frame width
318Spherical
319Stereo
320Video orientation
321Total bitrate

<tbody>
</tbody>
 

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
120

ADVERTISEMENT

This doesn't work unfortunately
 

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
120
I've taken a look at that link, but found that the Shell property file values for Beats-per-minute and Initial key should actually be 243 and 247, respectively. Also, I've re-read your original post. Accordingly, try the following instead...

Code:
varColumns = Array(166, 13, 21, 243, 16, 14, 247)

This works!! Thanks a lot!!

A small other question:
Is it possible to rename the added sheet to the specific folder name?
 

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
120
Is it possible to have the path to the source folder in cell A1 on sheet 1? So that I dont have to change the VBA code every time I want an other folder, but just only paste the new folder path in cell A1 on sheet 1.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,911
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top