Dan Wilson
Well-known Member
- Joined
- Feb 5, 2006
- Messages
- 507
- Office Version
- 365
- Platform
- Windows
Good day. I am running Excel out of Office365 (latest version) from Windows 10 Home. This is going to take a while to explain. Over the last 20 years I have amassed a music library (hereafter called the library) that I used when I was hosting a weekly radio show. After retiring from the corporate world and the radio show, I am now producing a weekly podcast using the library as the source for the music. For the last few years I have been receiving help from the many Excel users in this forum which have taught me many things about using Excel. Everything was working well until I purchased some software to enable me to produce the podcast as if I were doing it live on the air.
The new software which I will call OTS extracts data from the library for sorting and playlist generation. Herein lies the problem as the OTS examines all the files in the library and adds its own file to the library.
There is a workbook called Music that I use to make reference to the songs in the library. I have created a large set of Macros in the Music workbook to make Excel examine the files in the library and create a worksheet containing 8 Columns of data using the File Properties of the song files in the library. There are currently 4564 songs in the library and that keeps growing as I find new songs that I want to use in the Podcast.
The 8 Columns of the Music worksheet are-
Name – the Title of the song
Artist – the Artist of the song
Mult – indicates how many songs have the same Title with different Artists
Year – the Year the song was recorded
# - the highest rating the song received in the Billboard Chart
Lngth – the length of the song in minutes and seconds
Intro – the amount of time before the Artist begins singing
Genre – the Genre of the song
To explain the Column labeled as Mult further, it allows me to have a song in the library that has been recorded by more than one Artist by adding a number to the end of the filename. For example, the song “Alone” is in my library by 3 different Artists. Therefore, my library shows filenames of Alone 1, Alone 2 and Alone 3. I have contacted OTS Support and this is not a problem with their Search process as they search the ID3Tag elements rather than the filename.
Back to the problem. The library contains 4564 filenames ending in MP3. For example, Alone 1 will be listed as Alone 1.MP3 in the library. The OTS software examines the entire library and for each song found it creates a separate file with its own filename extension. For example, Alone 1.MP3 will have a separate file titled Alone 1.MP3.OMX making the library now contain 9128 files. The OMX file contains a pointer used by the OTS software to find the desired song.
The existing Macros that have taken years to complete still work, but with a complication. The process of running the Macro to create the worksheet used to take quite a while to process 4500 songs. I recently converted the hard drive in my computer to an SSD Hard Drive. The process took less than 5 minutes after that change. Now that the OTS software has added the OMX files and doubled the size of the library, the process now takes at least 10 minutes which is still not the problem. The problem is that after running the Macro, the worksheet now contains not only the 4564 songs, but also contains 4564 empty Rows of data following the first 4564 Rows. This is proven by the fact that when I execute a “Control End” function, the cursor ends up in Row 9129 (Row 1 contains headers). The Macro contains steps that draws border lines in and around the 8 Columns for printing purposes. I’m not sure how, but the Macro formatting the borders only formats the rows containing data.
I understand that there is more than one way to solve this issue.
I could manually delete the 4564 empty cells
I could manually define the Print Area to include only the bordered cells, but that would have to be changed every time I add more songs to the library.
I could manually select the first 4564 Rows to be printed.
I am hoping that there is way to edit the search process in the Macro to examine only the filenames that end with .MP3 and skip the filenames that end with the .OMX extension. I look forward to any help with this. I am willing to forward any or all Macros to anyone who is interested in tackling this issue.
Thank you,
Dan Wilson…
The new software which I will call OTS extracts data from the library for sorting and playlist generation. Herein lies the problem as the OTS examines all the files in the library and adds its own file to the library.
There is a workbook called Music that I use to make reference to the songs in the library. I have created a large set of Macros in the Music workbook to make Excel examine the files in the library and create a worksheet containing 8 Columns of data using the File Properties of the song files in the library. There are currently 4564 songs in the library and that keeps growing as I find new songs that I want to use in the Podcast.
The 8 Columns of the Music worksheet are-
Name – the Title of the song
Artist – the Artist of the song
Mult – indicates how many songs have the same Title with different Artists
Year – the Year the song was recorded
# - the highest rating the song received in the Billboard Chart
Lngth – the length of the song in minutes and seconds
Intro – the amount of time before the Artist begins singing
Genre – the Genre of the song
To explain the Column labeled as Mult further, it allows me to have a song in the library that has been recorded by more than one Artist by adding a number to the end of the filename. For example, the song “Alone” is in my library by 3 different Artists. Therefore, my library shows filenames of Alone 1, Alone 2 and Alone 3. I have contacted OTS Support and this is not a problem with their Search process as they search the ID3Tag elements rather than the filename.
Back to the problem. The library contains 4564 filenames ending in MP3. For example, Alone 1 will be listed as Alone 1.MP3 in the library. The OTS software examines the entire library and for each song found it creates a separate file with its own filename extension. For example, Alone 1.MP3 will have a separate file titled Alone 1.MP3.OMX making the library now contain 9128 files. The OMX file contains a pointer used by the OTS software to find the desired song.
The existing Macros that have taken years to complete still work, but with a complication. The process of running the Macro to create the worksheet used to take quite a while to process 4500 songs. I recently converted the hard drive in my computer to an SSD Hard Drive. The process took less than 5 minutes after that change. Now that the OTS software has added the OMX files and doubled the size of the library, the process now takes at least 10 minutes which is still not the problem. The problem is that after running the Macro, the worksheet now contains not only the 4564 songs, but also contains 4564 empty Rows of data following the first 4564 Rows. This is proven by the fact that when I execute a “Control End” function, the cursor ends up in Row 9129 (Row 1 contains headers). The Macro contains steps that draws border lines in and around the 8 Columns for printing purposes. I’m not sure how, but the Macro formatting the borders only formats the rows containing data.
I understand that there is more than one way to solve this issue.
I could manually delete the 4564 empty cells
I could manually define the Print Area to include only the bordered cells, but that would have to be changed every time I add more songs to the library.
I could manually select the first 4564 Rows to be printed.
I am hoping that there is way to edit the search process in the Macro to examine only the filenames that end with .MP3 and skip the filenames that end with the .OMX extension. I look forward to any help with this. I am willing to forward any or all Macros to anyone who is interested in tackling this issue.
Thank you,
Dan Wilson…
