Help with VBA dropping data

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
425
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
425
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 5, 2006
Messages
425
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can bypass that problem like
VBA Code:
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

Active Member
Joined
Feb 5, 2006
Messages
425
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,982
Messages
5,575,361
Members
412,656
Latest member
georgean
Top