Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create a list of media files names in excel.
I'd like information concerning how to create a list of the names of my media
files outside of cutting and pasting each individual name. How can I import these names into Excel without having to complete this task cutting and pasting each individual file name? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create a list of media files names in excel.
Several methods to accomplish this.......I like Tushar's best if importing to
Excel. To add a "Print Directory" feature to Explorer, go to this KB Article. http://support.microsoft.com/default...EN-US;q272623& Or you can download Printfolder 1.2 from..... http://no-nonsense-software.com/freeware/ I use PF 1.2 and find it to be more than adequate with custom features. OR Go to DOS(Command) prompt and directory. Type DIR MYFILES.TXT All the above create a *.TXT file which can be opened in Notepad or Excel. One more method if you want to by-pass the *.TXT file and pull directly to Excel is to use Tushar Mehta's Excel Add-in. This allows filtering and sorting once you have the data in Excel. http://www.tushar-mehta.com/ scroll down to Add-insDirectory Listing. Download the ZIP file and un-zip to your Office\Library folder. On Sun, 8 Jul 2007 09:00:01 -0700, joye68 wrote: I'd like information concerning how to create a list of the names of my media files outside of cutting and pasting each individual name. How can I import these names into Excel without having to complete this task cutting and pasting each individual file name? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create a list of media files names in excel.
I put together this that is rather tailored to your request. You can point
it at a folder and it will pull out only the media file types (graphic, video, audio) even if there are other types in that folder. You can put this code into a workbook of your own or you can download a working file at (click link and save to your hard drive) http://www.jlathamsite.com/uploads/L...ctedFolder.xls to put the code in a workbook of your own, press [Alt]+[F11] to open the VB Editor, choose Insert | Module from the menu and then copy this code and paste it into the module. Close it and run from Tools | Macro | Macros... Sub GetMediaFileNames() Dim basicPath As String Dim anyFileName As String Dim fileExt As String Dim rOffset As Long Dim baseCell As Range With Application.FileDialog(msoFileDialogFolderPicker) .Show If .SelectedItems.Count 0 Then basicPath = .SelectedItems(1) & "\" Else Exit Sub ' user cancelled End If End With If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 version of Excel rOffset = Range("A" & Rows.Count).End(xlUp). _ Offset(1, 0).End(xlUp).Row - 1 Else 'in Excel 2007 (or later?) rOffset = Range("A" & Rows.CountLarge).End(xlUp). _ Offset(1, 0).End(xlUp).Row - 1 End If Set baseCell = Range("A1") Application.ScreenUpdating = False ' improves speed anyFileName = Dir$(basicPath & "*.*", vbNormal) Do While anyFileName < "" If InStr(anyFileName, ".") Then fileExt = UCase(Trim(Right(anyFileName, Len(anyFileName) - _ InStrRev(anyFileName, ".")))) Select Case fileExt 'graphic files (not video) Case "JPG", "TIF", "TIFF", "BMP", "GIF", "PNG" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'windows media file extensions Case "WMV", "WMA", "WVX", "WAX", _ "ASF", "ASX", "WMS", "WMZ", "WMD" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'other media file extensions 'K-Jofol Case "WAV", "MP3", "VQF", "AAC" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'Liquid Audio Case "LAV" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 '(more) Microsoft Media Case "WAV", "MIDI", "SND" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'ModPlug and WinKaraoke Case "MOD", "KAR" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'Video types Case "MPEG", "AVI", "MOV", "VDO", "VIVO" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'QuickTime Case "3DMF", "3GPP", "AMC", "AMR", "DLS", "QCP", "SDP", _ "SDV", "SF2", "SGI", "SMIL" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'iTunes Case "M4A", "M4B", "M4P", "M4V" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'other types Case "XDM", "RAM", "RM", "AIFF", "DV", "AU", "FLA", "VDU", _ "M3U", "VR" baseCell.Offset(rOffset, 0) = anyFileName rOffset = rOffset + 1 'non media file encountered Case Else 'do nothing End Select anyFileName = Dir$() ' get next filename End If Loop Application.ScreenUpdating = True End Sub "joye68" wrote: I'd like information concerning how to create a list of the names of my media files outside of cutting and pasting each individual name. How can I import these names into Excel without having to complete this task cutting and pasting each individual file name? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a list (Word) of the names on Excel worksheet tabs | Excel Worksheet Functions | |||
Renaming scan files with a list of drawing names in excel spreadsh | Excel Discussion (Misc queries) | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
Can anyone help me Create Excel list of files in windows folders | Excel Worksheet Functions | |||
create a list of worksheet names (from a single folder, or open files) | Excel Discussion (Misc queries) |