How to Prompt a user to select a Folder.
 

Hi,

I am using the following codes to track our all the Excel file in a
specific folder.

Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "D:\Aki\Data\Excel\Formats\*.xls"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub


Its working fine but the the i want that system should prompt me to
select the folder. I mean to say that as soon as the macro is started
it should ask to choose the folder with the browse function. and as
soon as the user selects a folder and click on Ok it should display
the list of excel files in the specific folder.

How can we do that.

Thanks

Akash Maheshwari

How to Prompt a user to select a Folder.
 

See this page at John Walkenbach's site:

http://www.j-walk.com/ss/excel/tips/tip29.htm

--
Regards,
Tom Ogilvy


"Akash" wrote:

Hi,

I am using the following codes to track our all the Excel file in a
specific folder.

Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "D:\Aki\Data\Excel\Formats\*.xls"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub


Its working fine but the the i want that system should prompt me to
select the folder. I mean to say that as soon as the macro is started
it should ask to choose the folder with the browse function. and as
soon as the user selects a folder and click on Ok it should display
the list of excel files in the specific folder.

How can we do that.

Thanks

Akash Maheshwari

How to Prompt a user to select a Folder.
 

Akash,

See www.cpearson.com/excel/BrowseFolder.aspx for example VBA code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Akash" wrote in message
oups.com...
Hi,

I am using the following codes to track our all the Excel file in a
specific folder.

Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "D:\Aki\Data\Excel\Formats\*.xls"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub


Its working fine but the the i want that system should prompt me to
select the folder. I mean to say that as soon as the macro is started
it should ask to choose the folder with the browse function. and as
soon as the user selects a folder and click on Ok it should display
the list of excel files in the specific folder.

How can we do that.

Thanks

Akash Maheshwari

How to Prompt a user to select a Folder.
 

Especially the note at the bottom of that page regarding XL 2002 onwards..
--
p45cal


"Tom Ogilvy" wrote:

See this page at John Walkenbach's site:

http://www.j-walk.com/ss/excel/tips/tip29.htm

--
Regards,
Tom Ogilvy


"Akash" wrote:

Hi,

I am using the following codes to track our all the Excel file in a
specific folder.

Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "D:\Aki\Data\Excel\Formats\*.xls"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub


Its working fine but the the i want that system should prompt me to
select the folder. I mean to say that as soon as the macro is started
it should ask to choose the folder with the browse function. and as
soon as the user selects a folder and click on Ok it should display
the list of excel files in the specific folder.

How can we do that.

Thanks

Akash Maheshwari