trying to copy a list of file names.

**semidevil** · 10-28-2010, 07:08 PM

I have a ** with about 100 word documents. I want to copy all the word document title on a spreadsheet.

Through DOS, I was able to pull up each directory in my **, and it will display the list of file names, which I can then copy and paste onto excel.

However, it does not list the complete title.

I.E, instead of "word_document.doc" it would say "word_docu"

How do I display the full title? Is there a limit in DOS?

**davesexcel** · 10-28-2010, 07:24 PM

VBA would be required to get a list of files from a folder,
this one creates a list from a folder and creates a hyperlink so you can click a file to open it.

I originally had it to find pics in a Folder(hence the title) but it should work to find .docs, it does work to find .xls file type right now.

   Sub ListPics()

    Dim lCount As Long, ws1 As Worksheet, rng As Range

    Set ws1 = Worksheets("Find")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next
    Range("A5:A65536").Clear

    With Application.FileSearch

        .NewSearch
        .LookIn = "C:\" & "Excel" & "\"    'change this to your folder name
        .Filename = ".xls"    ' change this to your file type ie .doc
        If .Execute > 0 Then

            For lCount = 1 To .FoundFiles.Count
                If InStr(.FoundFiles(lCount), f) > 0 Then
                    ActiveSheet.Hyperlinks.Add Anchor:=Range("A65536").End(xlUp).Offset(1, 0), Address:=.FoundFiles(lCount), TextToDisplay:=Replace(.FoundFiles(lCount), "M:\CA\jobs\", "")

                End If
            Next lCount

        End If

    End With

    rng.Font.Size = 28
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

**BlastRanger** · 10-30-2010, 02:30 AM

Yo,
Another solution, see below post :
http://www.excelforum.com/excel-gene...eet-email.html

**martindwilson** · 12-12-2010, 07:17 PM

my command prompt window gives this(extract)
03/08/2009 12:46 <DIR> Start Menu
17/06/2008 18:05 10,462 test.txt
13/12/2010 00:15 19,456 this is a long word document.doc
05/04/2010 12:16 <DIR> tide

**Jack in the UK** · 12-22-2011, 08:56 AM

Yes DOS restricts to a CHAR value of 8 - 9 so the names your being given are correct,

The work around to to *** / export to say excel as the posts above have explained

Just for fun if Jack recalls correct

Try this DOS hack, i assume you are to teh level re dos commands and their usage, if not let me know

Open CMD
Navigate to your parent Folder
Type: Take not of the spaces
The above command will take the list of all the files and all of the information about the files, including size, modified date, etc., and send that output to the print.txt file in the current directory
dir > print.txt

This command would print only the file names and not the file information of the files in the current directory
dir /b > print.txt

This command would print only the file names of the files in the current directory and any other files in the directories in the current directory
dir /s /b > print.txt

Or another hack is to type
Notepad print.txt and the file will open ; copy and paste in to Excel etc