Help with VBA dropping data

[Dan Wilson]

Good day all. I am running Excel out of Office 365 (updated) on Windows 10 Home. A few months ago I was given a Macro to help with extracting File Properties from Music Files. After running this Macro several times I find that the code is dropping some files. My Music folder contains 4550 files. The Macro is showing 4438 files in a worksheet titled DB. Coincidentally, after the DB worksheet is created (10 minutes), executing a Control-End command selects Column G Row 4551 with Rows 4439 through 4551 being empty. The Control-End selects the correct location for having searched 4550 files with a header column. I have copied the Macro below if anyone wants to examine it to find a problem. I have tried editing the Macro to search a music folder with less than 100 songs and it works correctly. I appreciate any help. Thank you, Dan...

Sub get_file_properties_DB_1()
' 11/14/2020

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\Daniel\Music\MP3 Normalized") 'change the path to the source folder accordingly

varColumns = Array(0, 13, 15, 26, 27, 24, 16)

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
Columns("A:A").Select
Selection.NumberFormat = "@"
ActiveSheet.Name = "DB1"

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

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select

End Sub

 

[ExcelGzh]

What you are doing in your first loop involving the variable i is creating an array with the same number of rows as all the files in your target folder. Then in your second loop involving the variable i you are filtering the list to only use .mp3 and .wav files. I suspect that although your folder contains 4550 files only 4438 of them are .mp3 or .wav files so the last 112 rows of your array contain no data.

 

[Dan Wilson]

Good day ExcelGzh and THANK YOU for responding. I was at the point where I was going to try and isolate the missing records by running a comparison between the music folder and the worksheet, but 5,000 files would take a long time. I will examine the music folder and see what I can find. Thank you again.
Dan...

 

[Dan Wilson]

Good day ExcelGzh! Congratulations! Your response was right on target. I searched the music folder and found 112 files with a file type of MP3 instead of mp3. I fixed the 112 files by changing the MP3 to mp3 and reran the Macro. It found 4553 files and did not drop one. I must admit that I would never have found the problem without your help. I like to think that I am average when it comes to Macros, but apparently I'm not as good as I thought. I can't thank you enough for finding that.
Well done,
Tan Wilson...

 

[Fluff]

You can bypass that problem like

If LCase(Right(strFilename, 4)) = ".mp3" Or LCase(Right(strFilename, 4)) = ".wav" Then

then it doesn't matter what case the extension is.

 

[Dan Wilson]

Good day Fluff. thank you for the additional response. Once the problem was isolated to the MP3 versus mp3 capitalization, I realized that by examining the code, I could modify that line to get rid of the WAV as well since all of my music is mp3 format. I appreciate the extra help.
Dan Wilson...

 

[Fluff]

Glad to help & thanks for the feedback.