Software

Office challenge: What's a quick way to get a list of file names into an Excel spreadsheet?

Learn the answer to last week's Word challenge and test your Excel skills in this week's new challenge.

A user needs to list file names in an Excel sheet. The information's right there in front of her, but how can she get it from her screen into an Excel sheet? You could spend a while writing a VBA procedure, but let's skip that route. There's a quick an easy way to get file names into an Excel sheet--what is it? You aren't restricted to applications—you may use whatever tools you like.

Last week we asked…

What's that red x and where are my graphics? Gbentley suggested checking the Show Picture Placeholders option. This suggestion is good, but not quite right. Placeholders appear empty; they don't display a red x. For future knowledge, it's good to know though, so thanks Gbentley!

The red x indicates a broken link to a linked image file. This happens when you move a linked file or you move the Word document and forget to also move the linked image file. In addition, this can happen if you rename or delete the linked file. Word 2007 goes a long way toward clearing up the mystery by actually telling you what's wrong—you won't find this error message in earlier versions.

The easiest solution is to avoid the problem altogether by embedding instead of linking to the image file. That's not always practical though. Perhaps the next easiest solution is to simply relink the image file—Insert | Picture, and so on. It's quick and easy.

You can repair the link, but the process is a bit lengthy and easy to forget unless this problem occurs so often that you memorize it (and if that's the case, you need to rethink your strategy). To repair a broken link to an image file, do the following:

  1. In Word 2010, click the File tab and choose Info in the left pane. In Word 2007, click the Office tab and click Prepare in the left pane.
  2. In the far-right corner, click Edit Links to Files.
  3. Select the link and click Change Source.
  4. Use the dialog box to locate the file.
  5. Double-click the file.
  6. Click OK.

In Word 2003, do the following:

  1. Select the broken graphic.
  2. Press [Alt]+[F9] to display the field code.
  3. Correct the path.

Frankly, it's simpler to just relink the graphic, unless doing so plays havoc with your formatting.

Stay on top of the latest Microsoft Windows tips and tricks with TechRepublic's Windows Desktop newsletter, delivered every Monday and Thursday. Automatically sign up today!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

65 comments
kironkasbekar
kironkasbekar

There is a very simple way of doing this in Organyze 2.1 (see www.organyze.com). Select one or more folders or sub-folders (they can even be folders from different drives) select all or only some file formats, and then click on "Get list" to get a single list of files from across the selected folders and sub-folders. Just click on Export, and the list will get to an Excel or CSV format, and will include details such as file name, path, created date, last modified date, file type, and size. It takes barely a minute to do this! You can also do this for folders, in which case you get these details plus number of sub-folders, and number of files inside each folder.

RU7
RU7

Using the .csv extension doesn't affect the dir.csv file, it is identical to the dir.txt file, except that when Opening them, the .csv will typically open it in Excel and the .txt will use Notepad. Size is an issue. Of course the bare switch (/b) eliminates the size from the directory. But, if you want size, you don't use the /b switch. Then the size is represented with commas so any size over 999 will send the filename to the next column. Another column offset for sizes over 999,999. The /a-d switch might also be desirable so the list doesn't have an additional row for each sub-directory. The real solution is to import the non-/b .txt file into Excel using fixed cell width. Then you have 5 wonderfully sortable columns: Date, Time, AM/PM, Size, Filename. The first 5 rows and the last 2 rows may be useful. If they are not, they can easily be deleted.

ambraun
ambraun

Try Directory Report http://www.file-utilities.com It can scan your directories and directly send the output to Excel. No need to first save it to a txt or csv file. Plus it can show MP3, EXE, MSI, AVI, WAV and MS-Office properties

hundgenn
hundgenn

Select the files to copy. Right click and Send them to mail client. Mail client will open with files attached with the file names listed in the body of the mail. Copy the file names from the body and paste them in Excel.

RU7
RU7

you have to do it frequently. Get a command prompt in the top directory yo want to list. Enter "dir /b /s /a-d > temp.txt". In Excel 2007 | Data tab Click "From Text" then navigate to and select temp.txt. Select the "Delimited" radio button. Click Next. Under Delimiters click so that only "Other" is checked and enter "\" in its field. Click Next then click Finish. Click OK to start at A1. Go to Sheet 2, cell A1. Enter "=OFFSET(Sheet1!$A$1,ROW()-1,COUNTA(Sheet1!1:1)-1,1,1)". A1 should now have the file name from the first row on Sheet 1. Find out how many rows on Sheet 1 are used. On Sheet 2 go to cell A1 and copy it. In the name box enter "A" followed by the number of rows used on Sheet 1 and press Enter. Press CTRL+Shift+Home. Paste. Click the column A header to select column A. Copy. Click the column B header to select column B. Paste Special | Values (or ALT+E, S, V Enter). Now you can delete Sheet 1 and column A on Sheet 2. This leaves you with a column of file names for the directory and all its sub-directories. Once I had it documented, I did a list of 11,000 in less than a minute.

homesjc
homesjc

DOS forever Goto to target directory invoke CMD, and use DIR *.* > anyfilename eg dir.dat. Use any appropriate filters. Open file in notepad, and cut and paste into where ever. One then needs to select which columns from the text in column on one wants for data. Quick and dirty. Can also get the lot, and then you need to sort the data out for various sub directories. Works with any dos command or prog running under Dos where one can use the redirect command ?>? to capture the screen output. Ofcourse one can write simple VBA macros to sort out the data to your desire. JohnH Perth

Jazz_Euph
Jazz_Euph

dir /b /a-d > list.xls /b is a bare listing without headers /a-d gets rid of directories

jcurrentrbh
jcurrentrbh

super simple solution from cmd: cd dir /b >> file_list.csv and now you have a .csv with all of your file names all in a column :) EDIT: Oops, looks like someone else already posted this.

nzimmerman67
nzimmerman67

Why not cmd dir /b > c:\files.txt Import into excel

bperrill
bperrill

DOS dir > to txt file? Then open in Excel

ps.techrep
ps.techrep

Given: FileNames are displayed on the screen in an unspecified Windows application, presumably a long list that would be time-consuming to rekeyboard. Entire operation must be performed in Windows without recourse to macros or applications that aren't in a default Windows installation. 1. Highlight the section of wanted material on the screen and use copy, (usually Ctrl+C), open notepad and paste it. Turn off wordwrap. 2. If necessary, insert returns to get each FileNamename on a SEPARATE line. 3. Save the file as text. 4. Open the file in Excel as a text/prn/csv file. 4. Use Excel's Data/Text to columns function with or as the delimiter. 5. Eliminate all unwanted columns. (optional) 6. If the names are to be entered into an existing Excel sheet, then copy the block of data and insert or paste it into the destination sheet.

harrylal
harrylal

I have found FreeCommander to be an excellent Windows Explorer replacement. It has a wonderful set of abilities (including the ability to save lists to a file) that far exceed what the MS product can do. To add to all that it doesn't need to be in the registry to work; put it on a flash drive and take it with you.

tadair
tadair

Open a DOS window, do a "DIR" and direct it to a text file (DIR > FILES.TXT), and import the text file into Excel.

mcmarr
mcmarr

Install "Send To Toys" Highlight all the files in the directory Right click the selected files, and click "Send to clipboard as name" Paste them into excle

ljbartel
ljbartel

I use the following VBA to not only collect file names but also return up to 40 other file properties. The properties are as follows: Path, Name, Size, Type, Date Modified, Date Created, Date Accessed, Attributes, Status, Owner, Author, Title, Subject, Category, Pages, Comments, Copyright, Artist, Album Title, Year, Track Number, Genre, Duration, Bit Rate, Protected, Camera Model, Date Picture Taken, Dimensions, Width, Height, Episode Name, Program Description, Date, Audio sample size, Audio sample rate, Channels, Company, Description, File Version, Product Name, Product Version & Keywords. Not every file will have all of the properties. I have an Excel template with these properties in the header row. I placed a button on my worksheet with a call to "GetFileProperties". Here is the module's VBA (this may have some word wrapping issues as posted here. sorry indent is lost too): ' This API declaration is used to return the ' UNC path from a drive letter (no trailing backslash). Private Declare Function WNetGetConnection Lib "mpr.dll" _ Alias "WNetGetConnectionA" _ (ByVal lpszLocalName As String, _ ByVal lpszRemoteName As String, _ cbRemoteName As Long) As Long Const ERROR_BAD_DEVICE = 1200& Const ERROR_CONNECTION_UNAVAIL = 1201& Const ERROR_EXTENDED_ERROR = 1208& Const ERROR_MORE_DATA = 234 Const ERROR_NOT_SUPPORTED = 50& Const ERROR_NO_NET_OR_BAD_PATH = 1203& Const ERROR_NO_NETWORK = 1222& Const ERROR_NOT_CONNECTED = 2250& Const NO_ERROR = 0 Sub GetFileProperties() Dim sDriveSpec As String Set objFSO = CreateObject("Scripting.FileSystemObject") Set objShell = CreateObject("Shell.Application") myFiles = Application.GetOpenFilename("All file types,*.*", , , , True) If Not IsArray(myFiles) Then Exit Sub j = Cells.SpecialCells(xlCellTypeLastCell).Row + 1 For Each myFile In myFiles i = InStrRev(myFile, "\") sFileName = Mid(myFile, i + 1) sFolderPathspec = Left(myFile, i) sDriveSpec = Left(sFolderPathspec, 2) Set objFolder = objShell.NameSpace(sFolderPathspec) Cells(j, 1).Value = UNCPath(sDriveSpec) & Mid(sFolderPathspec, 3) For i = 0 To 40 Cells(j, i + 2).Value = objFolder.GetDetailsOf(objFolder.ParseName(sFileName), i) Next j = j + 1 Next End Sub Function UNCPath(strDriveLetter As String) As String Dim lpszLocalName As String Dim lpszRemoteName As String Dim cbRemoteName As Long lpszLocalName = strDriveLetter lpszRemoteName = String$(255, Chr$(32)) cbRemoteName = Len(lpszRemoteName) lngreturn = WNetGetConnection(lpszLocalName, lpszRemoteName, cbRemoteName) If lngreturn = NO_ERROR Then UNCPath = Trim$(Replace$(lpszRemoteName, Chr$(0), "")) Else UNCPath = strDriveLetter End If End Function

TobiF
TobiF

To me, this code seems more straightforward (one more directly relying on built-in VBA functionality for browsing files) than the suggestions in a subthread higher up. But I have one question about these two lines: If Left(d_Dir, 1) "\" Then d_Dir = d_Dir & "\" Here you test if the string lacks a backslash in the BEGINNING of the string. If that is the case, then you add a backslash TO THE END of the string. Is that intentional?

Willysaef
Willysaef

I will use the CSV format rather than the txt format as the output file. The CSV format is the same as TXT, as every line will make a row, and every comma will start a new column. C:\>DIR /S /B > Out.csv open the Out.CSV file in Excel. :)

TobiF
TobiF

1. Open command window in needed folder (With powertoys on the system, it's just a right click in the folder tree) 2. Enter command dir /b > dirlist.txt 3. Open dirlist.txt in notepad and copy what's needed into Excel. (Note that the file dirlist.txt itself will also be listed, you may want to delete it) /b gives just the file names, without dates and sizes.

TobiF
TobiF

Unless you want to rip the filenames from a directory with 200 photos of 3 Mb each...

rki
rki

Select any number of files in a folder. Ctrl+C Paste to FileGrab Window. Re-Select. Ctrl+C Paste to any application.

Ritch_is
Ritch_is

Since the challenge stipulates that I can use any tool I like, I'll use my Mac. 1. Select files in a Finder window. 2. COMMAND-C to copy 3. Click in the starting cell in Excel 4. Paste.

heybahler
heybahler

The File Viewer in IrfanView also has this feature. File > Save selected files as TXT.

TobiF
TobiF

I really like it!

TobiF
TobiF

In a normal installation of Excel, double-clicking a csv file will immediately call up Excel. (When I need a list of files, the target is usually Word.)

vanhaven
vanhaven

cut out the middle man... dir /b > dirlist.xls

radams36
radams36

Yep, this is also how I'd do it. Well done! I was working Help Desk for Ericsson years ago when a fellow HD'er was talking about the ridiculous request that a user had made to print out their file directory. When I explained to them how to redirect DIR's output to the printer or to a file, and demonstrated, they were completely BAMboozled. It's still good to know DOS....

clucier
clucier

Some things are done better with the old DOS piping commands. I was going to write the same thing, minus the "/b", and then say that the file names could be parsed from the size and dates within Excel.

eanderson
eanderson

You can forgo the step of outputting to a text file by using the "clip" utility to copy the text straight to the clipboard: dir /b | clip I believe "clip" has been included since the release of Windows Server 2003. It's also available in the various Resource Kits for earlier releases including Windows XP.

kmk2
kmk2

Gotta love the effort of all those scripts above, but this is the easiest method I know of which I've been utilizing for years.

Neon Samurai
Neon Samurai

Offhand, is there an Windows equivalent of "&&"? I have a few apps I open from command line. "/path/app1.exe & /path/app2.exe" and up they both pop. What I'd like to do is this: dir /d > tmp.txt && notepad tmp.txt But, I need a Windows equivalent of "&&" though cmd.com does recognize "&".

jamesski
jamesski

You also have to take out the . and .. at top and number of files, size & etc at bottom

nmaguireznet
nmaguireznet

1) get a command prompt. 2) navigate to the dir you want. 3) use the old DOS redirection eg. dir > list.txt instead of displaying the dir on the scree, this will create a file list.txt and load the dir list into it. 4) now comes the part i can't quite remember and since i don't presently have access to excel, i'll do my best. open a blank spreadsheet and import (or open) the list.txt file. use the sliders to set the cell widths for the file list only. ignore the "header". viola! one speadsheet c/w directory. delete the "header" and "footer" garbage and look like a superstar in about two minutes. if you want the header and footer, just import it again without any cell delimiters and copy the untouched info into the appropriate cells. the DOS redirect will also append files if you use >> instead of > and it will also support pathnames in the output file.

Glenn from Iowa
Glenn from Iowa

I use the free (for personal use - same as "Karen's" tools) ASAP Utilities available at http://www.asap-utilities.com/. It imports file names and properties, including or excluding subfolders, and can create hyperlinks in Excel linking to the folders. You can also use wildcards to limit it to certain file patterns or files modified/created in the past x days. And that's only one of the more than 250 utilities and worksheet functions included. If you use Excel and haven't downloaded this utility, I would recommend it! Sorry to sound like an ad, but I do use it at least weekly, if nothing else, to auto-fit the row height on merged cells. And yeah, I'm sure it uses VBA to do it, but at least I don't have to write it. ;-)

ljbartel
ljbartel

My favorite is http://pathcopycopy.codeplex.com/. I especially like the option to get UNC path from mapped drive leters. Send somone a UNC path and they are more likely able to access the same.

Ray Baker
Ray Baker

Highlight the file name(s). Right click. Select Copy Filename. Copy Filenames is a little add-in program that I found very helpful. See ExtraBit software.

DelbertPGH
DelbertPGH

This thing runs as a macro, depends on no external program. Has name, size, and date in separate columns... not sure if more columns are programmable. By the way, THIS HAS TO BE IN A MODULE or it will not work. (As Samir pointed out.) In the VBA editor, in the Project tile, right-click on the spreadsheet and insert a module. Then copy the code below into the module. Sub ListFiles() Msg = "Select a location containing the files you want to list." Directory = GetDirectory(Msg) If Directory = "" Then Exit Sub If Right(Directory, 1) "\" Then Directory = Directory & "\" r = 1 ' Insert headers Cells.ClearContents Cells(r, 1) = "FileName" Cells(r, 2) = "Size" Cells(r, 3) = "Date/Time" Range("A1:C1").Font.Bold = True r = r + 1 ' Get first file f = Dir(Directory, 7) Cells(r, 1) = f Cells(r, 2) = FileLen(Directory & f) Cells(r, 3) = FileDateTime(Directory & f) ' Get remaining files Do While f "" f = Dir If f "" Then r = r + 1 Cells(r, 1) = f Cells(r, 2) = FileLen(Directory & f) Cells(r, 3) = FileDateTime(Directory & f) End If Loop End Sub Function GetDirectory(Optional Msg) As String Dim bInfo As BROWSEINFO Dim path As String Dim r As Long, x As Long, pos As Integer ' Root folder = Desktop bInfo.pidlRoot = 0& ' Title in the dialog If IsMissing(Msg) Then bInfo.lpszTitle = "Select a folder." Else bInfo.lpszTitle = Msg End If ' Type of directory to return bInfo.ulFlags = &H1 ' Display the dialog x = SHBrowseForFolder(bInfo) ' Parse the result path = Space$(512) r = SHGetPathFromIDList(ByVal x, ByVal path) If r Then pos = InStr(path, Chr$(0)) GetDirectory = Left(path, pos - 1) Else GetDirectory = "" End If End Function

radams36
radams36

Efficient - I like it. And WHY does this thread have SO MANY posts that start with 'use X software' when the premise is to accomplish the task as "a quick way" to get the filenames in Excel. Sorry, any solution that starts with "install X software" is not only not meeting the criteria, it fails in a way I can only describe as UTTERLY LAME.

Niall Baird
Niall Baird

That was the same as I did. When you open, its a fixed length file, just adjust the columns as necessary.

TobiF
TobiF

I keep forgetting about the "| clip" trick all the time. Very convenient.

patobrien.3g
patobrien.3g

The "&&" on a command line is used for a conditional combination. The second command will execute only if the first command completes successfully. Note: the cmd definition of "successfully" may not be what you expect. Also, if you use the double pipe: ||, then the second command will execute only if the first command does NOT complete successfully.

LocoLobo
LocoLobo

Works well, never had problems that way.

stuartc
stuartc

You say the old days, I would still use this today, just notice peoples replies about error they get in the Vb Script, why bother? Just go command prompt.

RandyLyon
RandyLyon

I searched the user guide and read every occurrence of the word "file" and the word "directory" and can't find how to get a directory list into Excel. I don't doubt its there but short of reading all 200 pages how do you find it?

Answers
Answers

I definitely recommend ASAP Utilities. Also because not only do you get the list of files, but the properties, subfolders and links back to the files options is so useful and it only takes seconds to do.

DelbertPGH
DelbertPGH

It's typed public. If you have the workbook containing this macro open, it will be visible in the macro menu of any other workbook you have open at the same time.

busby42
busby42

Tried to use this, but got error in line: "Dim bInfo As BROWSEINFO" 'Compile error...User-defined type not defined' Have I done something wrong or is this for a different version of excel (I am using 2007)? Thanks!

TobiF
TobiF

I like to know different ways of doing things. Depending on the situation, I employ the approach that fit me best in a given situation. An approach is useful if: - I can remember and have access to it when I need it and - It will do what I need. People are different and have various needs. If you need to daily create filtered and formatted file lists in Excel (maybe you're administrating back-ups of a site..) then, of course, you may create the perfect, automated tool for this. VBA would be a good candidate, or maybe even some commercial specialized software. I may need a list of files in a Word table once a month or two. I prefer keyboard over mouse and know my ways around DOS, so for this particular situation, DOS is fine with me. However, I have a colleague, who is more point-and-click. For her, DOS is not so convenient. But she always carries a couple of USB drives with her. A little piece of freeware on her stick would be perfect for her. And, that's of the reasons I'm going to this site. I want to learn new ways of doing things. For someone, the DOS approach is an eye-opener. (Or the fact that so many people still use the CLI for different things, long after DOS itself "died", when CLI became an add-on to Windows, rather than the other way around.) For me, it's good to remember that somewhere on TR, I'll have a thread about different tools to enumerate files. So thanks for this topic. There's no need to pick THE winner. If we treat this ok, then we're ALL winners!

Neon Samurai
Neon Samurai

I must have missed the author's scoping document that specified "must be indicated that all tools are carried on the person at all times; must be tools already commonly carried by majority of techs; must not suggest alternatives to the minimalist method though they may work better for others". If I'm going to be doing this once in a while like every six months or some such thing then sure; "dir /d > tmp.csv && excel tmp.csv". If I'm going to be doing this regularly (daily, weekly, monthly or possibly quarterly) then you can bet I'm considering Excel Macros and third party utilities encase they in fact make the task more efficient. I don't need to reduce the time of twenty dir dumps very much to justify the small increase in time for the first dir dump. Having done heavy data input, business analysis and report generation; you can bet I'm going to take a hit on time the first if the result is less time every iteration after that. Anything that decreases the time for regularly repeated tasks requires consideration. Even now long past that particular job, I'm doing weekly data dumps from powershell which I'd like to make more efficient than two seporate commands typed in full. (it seems powershell doesn't like "&&" though I'm happy to be told and confirm that cmd.com does recognize it)

radams36
radams36

NO, in response to this challenge it ONLY makes sense to say "Use X Software that I carry around on a USB stick" IF the app is one that most people carry around on a USB stick (and since most people don't do that, shrewd though it may be, then BUZZ - thanks for playing....) OR if one is so completely self-absorbed as to assume 'everyone else does things the same way I do'. Reasonable assumptions that could prevail in this challenge would be that the user has Windows and has Office, as they do have Excel. Or, as some clever responses intimate, that they are on a Mac (although that still puts them in the minority). Secondly, it makes NO SENSE to install 'software X' if the same thing can be accomplished easily from a command prompt while having to install NOTHING. That doesn't even make sense in the long-term scenario you describe. My point remains that 'install X software' does not meet the basic criteria, or most efficiently meet the challenge as presented.

Glenn from Iowa
Glenn from Iowa

Normally, I would agree that using X software is a lame reply for a how-to post - but re-read the challenge. I hesitated to post about using a 3rd party app, but I posted above for these reasons: 1) it is quick 2) it's how I would do it - and have done it in the past 3) the app is free 4) others had posted about other tools and 5) the author of the challenge explicitly said "You aren't restricted to applications--you may use whatever tools you like." Is it for everybody - no! Would I have posted if the app were not free or if that were its only function - no. But it is a useful utility, and when I first downloaded it, I couldn't believe how useful it was. I know you'd drive yourself crazy trying to download every utility somebody recommends, but this is the one I love. I've tried the same thing using DOS, and if you're just trying to get filenames, using DOS works well. But trying to get file dates and sizes from multiple subfolders into Excel using DOS is a nightmare in parsing text. Using a utility is a quick, easy way to get the information you need. And she said we could use them! (edited - grrr, copy and paste from the actual article shouldn't paste characters that can't be displayed in the comments!)

Neon Samurai
Neon Samurai

What if one has X software on there USB as a portable app? Is it wrong to use an additional program when you have it right there and ready? Also, if this is a frequent task then installing a specific utility for it only slows you down the first time; after that your all set and gaining time as a result.

Neon Samurai
Neon Samurai

I was sure I got errors when trying "&&" in the past. It would have been with Powershell though as I'm in to it at least once weekly with a couple of commands I would have been chaining together. Will have to confirm next time I'm grabbing the weekly stats.

Glenn from Iowa
Glenn from Iowa

The easiest way to get to it is to press Ctrl+Alt+F. Don't worry; it will pop up a dialog box to choose options before actually creating the list. The full name is Create a list of filenames and properties in a folder...(Ctrl+Alt+F). My Favorite Tools is populated with 26 default items and you can find the one above at #6. It is also under Fill (#13), Information (#11), and Import (#11). Interestingly though, I tried a search using "file" and missed it among the dozens of items the first time around. But it is there and it works slick! :-) (edited to clarify My Favorite Tools)

mark.lardner
mark.lardner

Great add-in, it works in seconds and is accurate

DelbertPGH
DelbertPGH

If you want, I can send you a copy of the original spreadsheet that was on the C.D. that came with the Excel Bible book. Send me a private message, and I'll reply with the spreadsheet attached. It's Excel 2003, by the way.

TobiF
TobiF

The private procedures can be private, since they are only called by other procedures in the same module. I don't know about office 2007, but in office 2003, it was possible to convert a project like this into an add-in, which can then always be available, whenever the add-in is loaded.

busby42
busby42

Hi, I'm intigued as to what I am doing wrong, I really like your solution so would like to get it right if you can help? I started with a blank workbook, started VB editor and under the project tree for "VBAProject" I right clicked and selected insert module, I then copied the code (and eventually my additional Private Type code piece) into the module. This created a "Modules" folder in the project tree and a new "Module1" under it. I could then access the macro from the macros icon in the excel ribbon. I saved the workbook as "macros.xlsm". When I have this workbook open I can use the macro in any other open workbook. It clears the active sheet and then creates the table with filenames. I am using a "sparklines.xlam" add-in which appears in the project tree albeit separate and above the "VBAProject" item (wish I could attach a screen shot!). Thank you for the solution! Samir

DelbertPGH
DelbertPGH

It sounds like you've added the code to a particular worksheet. That's why you couldn't declare it public. Private code will only work when you're on the worksheet that contains it. Which is not to say that it's useless in that context; you can still do directories inside of Excel. It's just not as flexible. It's possible to create a "module" within an Excel workbook, which just serves as a container for code. All the worksheets in the workbook can access the module. Furthermore, if you declare the functions in the module to be public, then any other workbook open on the computer at the same time can access those functions. This isn't something that would necessarily seem important to somebody just trying to get his simple tasks done, but when you are a programmer trying to put together a system with lots of moving parts, this kind of flexibility is something you try to include, and take extra trouble to effect.

busby42
busby42

Hi, I added some code above the Sub and now seems to be working - I am not a coder so please correct me if I messed up! I cribbed the code from here: http://www.mrexcel.com/forum/showthread.php?t=503151 The full code as included in my module is: Private Type BrowseInfo ' used by the function GetDirectory hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) As Long Sub ListFiles() Msg = "Select a location containing the files you want to list." Directory = GetDirectory(Msg) If Directory = "" Then Exit Sub If Right(Directory, 1) "\" Then Directory = Directory & "\" r = 1 ' Insert headers Cells.ClearContents Cells(r, 1) = "FileName" Cells(r, 2) = "Size" Cells(r, 3) = "Date/Time" Range("A1:C1").Font.Bold = True r = r + 1 ' Get first file f = Dir(Directory, 7) Cells(r, 1) = f Cells(r, 2) = FileLen(Directory & f) Cells(r, 3) = FileDateTime(Directory & f) ' Get remaining files Do While f "" f = Dir If f "" Then r = r + 1 Cells(r, 1) = f Cells(r, 2) = FileLen(Directory & f) Cells(r, 3) = FileDateTime(Directory & f) End If Loop End Sub Function GetDirectory(Optional Msg) As String Dim bInfo As BrowseInfo Dim path As String Dim r As Long, x As Long, pos As Integer ' Root folder = Desktop bInfo.pidlRoot = 0& ' Title in the dialog If IsMissing(Msg) Then bInfo.lpszTitle = "Select a folder." Else bInfo.lpszTitle = Msg End If ' Type of directory to return bInfo.ulFlags = &H1 ' Display the dialog x = SHBrowseForFolder(bInfo) ' Parse the result path = Space$(512) r = SHGetPathFromIDList(ByVal x, ByVal path) If r Then pos = InStr(path, Chr$(0)) GetDirectory = Left(path, pos - 1) Else GetDirectory = "" End If End Function Thanks DelbertPGH, this is great for me... Samir

DelbertPGH
DelbertPGH

PUBLIC TYPE can only work at the module level. In the VBA editor, in the project window, right-click on the spreadsheet and insert a module, then copy the code into the module.

Editor's Picks