Hello I am Anthony, a 63yo retired engineering guy, trying to get my media together on my pc. I am using a Windows 7 machine with Excel 2003 (I know both very outdated, yet is all I have). I have been using excel for many years and probably forgot most coding I’ve learned over the years and I now am using recorded macros (and whatever VBA i can recall) to relearn things once again.

To get to the point, I have lots media on the machine and am using a free program called filelist by JAM Software. (Thank you very much) I use the JAM Software filelist program to create a .csv listing of my media and certain other data. I parse the .csv data in Excel and use recorded macros/VBA to sort it the way I like.

My most current stumbling block is, how to create dynamic hyperlinks down a column that changes frequently, and when I click on the filename it opens the default program on the machine. The issue is that the list is always changing as I add new media/data. I have recorded a macro function to manually create the static hyperlinks down the column. However I would like to use some Excel VBA to know where the end of column as I add more media/data is and dynamically fill in the hyperlinks.

I am thinking that it needs to count the rows and return the value somewhere to the VBA. I am uploading the code I have so far, and a sample workbook that represents the workbook after I run the VBA. If anyone can be so kind as to impart their wisdom on the VBA, I would be very grateful. Please feel free to make any revisions to the code (and if not too much trouble a brief explanation as to how each revision works and is better). I’m sure many others will learn from this as well. I will share the link to the free JAM Software if anyone would like it (I tried to put the link here and it wouldn't let me). It took me years to find this software in that it can give most all windows explorer data fed from the windows properties metadata. Specifically I could never find a way to get the Length of the media gleaned from Windows Properties Metadata.

The code works thus far in that it opens the media with the default program. If one desires to make the code operable put a path to the certain filename(s) in cells E2:E23 and likewise add the corresponding filename(s) in A2:A23.Currently I have somewhere in the neighborhood of 18000 media files. I plan on adding the requested hyperlink part of the code to each of the sheets with the exception of the Original, of course. Also note that the uploaded ExcelForumSampleWbkAFTERRunCode_ed8.xls workbook contains the code used, yet I'm also uploading a text file of code thus far. (Most accurate) Note I was going to add a before workbook yet it appears that I was only allowed to upload two itme

Two things I have found that i don't like with my existing code is that the color of the Hyperlink changes and would like to reset that once it is selected; and when I open the .csv and parse it in excel I have to rename the sheet from the default name it was given by excel. If I could have it select the sheet (whatever it is named by default) and rename it Original, that would be super. Also, ultimately, I would very much like the workbook to be dynamic (I'm thinking activex), in that the actual Windows data can be altered via the workbook. And lastly I am looking to put a drop down (combook/vlookup) at least on the Length sheet to search length column and return the rows containing the typed in time value. If it works out good. I'd like to use the drop down on all the sheets to look for other values (ie: filename; size; folder etc). If any clarification is needed do not hesitate to let me know. Thank you so much in advance.

Regards,
Anthony