Directory/Folder listing

[jeremyb]

Can someone help, I'm new to this site.

I have made use of some code I found on this forum (thanks Lucas it's great - can't email you to ask this question so had to enter forum) that will output the contents of a Folder or Directory into an excel sheet.

Only issue is that it lists everything - msoFileTypeAllFile.
I have amened the code to show only office files using msoFileTypeOfficeFiles.

Thing is I want it to list only .doc, .xls, .ppt, .mdb, .pdf, .dwf and .jpg

I have been unable to amend code to do what I need which is just to search on the file extensions that I enter into the macro.

Can anyone help

Thanks

Jeremy

[Oorang]

Please post the code you want modified.

[RonMcK]

... I have made use of some code I found on this forum (thanks Lucas it's great - can't email you to ask this question so had to enter forum) that will output the contents of a Folder or Directory into an excel sheet.
jeremyb,

You can send Lucas a Private Message. Click the Private Messages link under the line that says 'Welcome, jeremyb' and you'll be off and running.

Cheers!

[lucas]

Better to ask it here. We would rather share the questions and responses than do it in private. It is actually a board policy.

If I remember, that entry uses filesearch and will be obsolete with 2007.

Is this the one Jeremy?

http://vbaexpress.com/kb/getarticle.php?kb_id=781

[lucas]

It is a function of filesearch. This works for finding just excel files:
.FileType = msoFileTypeExcelWorkbooks

[lucas]

If you look up msofiletypes in the vbe help you will find this which will allow you many options for what to search for:

[lucas]

By the way, hi Ron. One of the boys that grew up here with our kids finished college and just landed a job in Orlando. Pretty good job with the FAA and they put him up in a condo on Turkey Lake Road while he was finding a place to live.....it is an amazingly small world.

[jeremyb]

Lucus, yes this was the code I am using - as detailed below with mods.

Sub listWordsub()
Dim i As Long
Dim Path As String
Dim Prompt As String
Dim Title As String
Dim TempArr() As String
With Application.FileSearch
.LookIn = "c:\"
.FileType = msoFileTypeWordDocuments
.SearchSubFolders = True
.Execute
For i = 1 To .FoundFiles.Count
Range("A" & i).Value = .FoundFiles(i)
Next i
End With

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
~Code Tags Added by Oorang



But I would like to list all files that end with doc, xls, ppt, mdb, PDF, DWF and Jpg all in one hit.
How can the code be changed to accomodate file extentions instead

Thanks in advance

Jeremy

[mdmackillop]

If you look up msofiletypes in the vbe help you will find this which will allow you many options for what to search for:
Hi Steve,
What version of Excel gives that listing?

[lucas]

Hi Malcolm,
Version 2003. Search for msofiletype and you will get the choices on the right.....select FileTypeProperty(office) and you will get the help window shown. Click on msofiletype to get the list I posted....

Edit: Marked items in red...

[jeremyb]

Many thanks but fixed the issue using Filename. Found the syntax for that which I was getting wrong between different file types. Using comma instead of semi colon.


Thanks


Jeremy

[Oorang]

In the alternative:
From the VBE press F2 to launch the object browser and search for msofiletype. The whole enumeration is listed there.
In the Second Alternative, just type
.FileType = msofiletype.
And when you type the second dot the intellisense will list the enum.
.FileType = msoFileType.msoFileTypeWordDocuments
Is legal syntax and in many cases I find the style to be preferred, as if I ever want to edit it later I can just backspace to the period and let the intellisense do the work for me.

[lucas]

Many thanks but fixed the issue using Filename. Found the syntax for that which I was getting wrong between different file types. Using comma instead of semi colon.


Thanks


Jeremy

Hey Jeremy, It would be nice if you would share your solution for future searches of the forum with the same or similar problem.....post the code please.