Hi Roger,
Thanks a lot, it works like a charm.
There are couple of very basic issues, could you pls resolve those as well.
Sorry I'm only good in copy/pasting.

1) How can I change default folder so that popup window opens in following
folder E:/2010/customers/

2) I got a following macro which I use to save the file. But now It saves in
the root folder instead of saving in specific folder which we set above with
BrowseForFolder function.

Sub SaveAsCell()
Dim strName As String

On Error GoTo InvalidName
strName = Sheet1.Range("B1")
ActiveWorkbook.SaveAs strName

Exit Sub
InvalidName: MsgBox "The text: " & strName & _
" is not a valid file name.", vbCritical, " "
End Sub

Thanks,
Kevin

"Roger Govier" wrote:

Hi Kevin

The code as it stands is et to only read the contents of the folder where
the workbook with the code is stored.
If you added the code created by Ken Puls as a function
http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
BrowseforFolder, then you could amend your code to let the user choose the
folder for listing

Change

' get the folder name of the active workbook
FolderName = ActiveWorkbook.Path

to

' choose the folder to be used for listing the files
FolderName = BrowseForFolder

The code for BrowseforFolder is

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
' written by Ken Puls
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.Self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function


You might also want to consider clearing your Destrange before you start, as
otherwise any files already listed will not be overwritten if the list from
the new folder is less than that last created.
--
Regards
Roger Govier



__________ Information from ESET Smart Security, version of virus signature database 4832 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com