Create and import filename list into excel

[Guest]

Want to be able to extract a list of filenames and import them into Excel or
Word.

Want a program or function or macro that works like many browse functions,
as in "file open" or "file save as", but, when you find the folder you want,
I want the function to at least create a text file that lists the file names
or much better still is available within Excel or Word and inserts the list
directly into the spreadsheet or document. A further advancement would be to
be able to list the filenames with or without associated file information,
rather like you can do with the "dir" command at the command prompt or like
"List" or "Details" as used in Windows. An enhancement would be to only list
a specified file type. e.g. only .doc or .jpg

 

[Guest]

Jim Semaj:

try,

Sub filelist()
Dim fd As FileDialog
With Application.FileSearch
..NewSearch
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show = -1 Then
rpath = fd.SelectedItems(1)
Else
MsgBox "has not selected the Folder": Exit Sub
End If
..LookIn = rpath
..SearchSubFolders = True
..Filename = "* .xls"
The
..Execute
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, "A"), Address:= _
.FoundFiles(i), TextToDisplay:=.FoundFiles(i)
Next i
Else
MsgBox "has not detected file"
End If
End With
End Sub

if you want only list *.doc filename,then replace
.Filename = "* .doc"

 

[Guest]

Having logged this question, I can now see that numerous other people have
been looking for a solution to the same or a very similar problem and that
various solutions have been suggested.

The Freeware "printfolders" available at
http://www.gmayor.com/downloads.htm gets more than half the job done but is
seperate from Excel and then the result has to be copied into Excel (or
Word). I have looked at some of the other suggested solutions and tried to
copy and paste to make an Excel macro but that doesnt seem to work for me
(Excel 2000).

I would like to get a solution that could be copied and pasted into a
personal macro and then attached to a macro button added to the Excel toolbar
or a ready-to-run add-in that could be added to Excel 2000 or 2003.

I know how to add a button to the tool bar and attach a Personal Macro to
that button but I don't actually know the procedure to add an "add-in".

With so many people interested in this problem, it is hard to understand how
Microsoft can be so complacent and just sit back doing nothing.
The ability to get file names from a folder and put them into Excel or Word
should be built into those programs. Similarly with Windows Explorer, it
should be possible to put the list of filenames into a text file or directly
print them out.

MICROSOFT are you listening???

With "printfolders" plus copy and paste or import data, I think I can
achieve what I want but something directly in Excel would be much simpler?
Any comprehensive suggestion would be appreciated.
Jim

 

[Guest]

MY SOLUTION
1. Download "Printfolders 1.2" from http://www.gmayor.com/downloads.htm
2. Instal with shortcuts on Desktop and Start > Programs.
3. Created folder: C:\PrintFolders_Output
4. Run PrintFolders.
5. Set Output file to: C:\PrintFolders_Output\Filename_List.txt
(NEVER CHANGE THE OUTPUT FILE LOCATION OR NAME)
6. Create Desktop shortcut to this file (Optional).
(USE TO PRINT LIST OR COPY AND PASTE INTO WORD.)
7. Run Excel and record Macro in Personal Workbook.
using "Data > Get External Date > From TextFile"
8. Slightly modified resultant VBA Macro for Excel 2000 as follows:
_________________________________________________________

Sub Import_Filename_List_ex_PrintFolders()
'
' Import_Filename_List_ex_PrintFolders Macro
' Macro recorded 13/12/2005 by xxxxxx
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\PrintFolders_Output\Filename List.txt", _
Destination:=Selection)
.Name = "Filename List_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 4
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With
End Sub
____________________________________________________________

The key modification was to change the "Destination:=" to "Selection".
The recording set the destination to a specific Cell "Range (C4)"
where the cursor was when the Macro was recorded.

You can also edit the path and name of the source file in the Macro.

The macro may require changes if you wish to parse the data differently.
I am just importing bare filenames.
If you only have one requirement and do this in the original macro

recording, then the macro will incorporate the appropriate action.
However, if your requirement is variable, there would be a problem.
_____________________________________________________________

9. Using Tools > Customise > Commands"
Insert Macro Button on Toolbar by Drag and Drop.
10. Right click on button:
- Asign the above Macro.
- Copy Button Image from "Data > Import Textfile".
- Paste to the new Button.
- Set display to "Default Style".
11. Rename Button: &Import Filename List ex 'PrintFolders'

RESULT

Use "Printfolders" to create the list.
Then, in Excel,:
Place Cursor in desired top left cell.
Click on the new Macro Button.
DONE "Instantly"

If you could "Call" "PrintFolders" from within Excel you could have a one

step solution for Excel but the above provides the advantage of being able

to use the filename list elsewhere than just in Excel.

I'm pretty happy.