Help with VBA dropping data

Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
504
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
Solution
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...
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top