Listing of Files and Worksheets

[Krishnakumar]

Hi All,

Self explanatory Title

I hope it's useful to atleast some of you !

Note: Change the path before clicking the button.

[Dave Hawley]

Thanks, Kris.

[Ger Plante]

Nice work Kris... thats a keeper.

[Krishnakumar]

Hi,

Thanks Dave, Ger.

[dcraker]

Wow it works good. Even shows the names of the sheets not visible also.

Simply amazing Kris

[Krishnakumar]

Hi,

Thanks David

[Krishnakumar]

A modified version.

[Dave Hawley]

Kris, why not use;

With Application.FileDialog(msoFileDialogFolderPicker) 
    .Show 
    FilePath = .SelectedItems(1) 
End With 

to get the folder path?


Anyway, would you mind if I used this in our next Newsletter? I will, of course, give you full credit and a link to your blog, so get some AdSense set-up

[Krishnakumar]

Hi Dave,

That won't work in 2000 version.

Anyway, would you mind if I used this in our next Newsletter? I will, of course, give you full credit and a link to your blog, so get some AdSense set-up

Go ahead.

[Ger Plante]

Hi Kris, I know its slightly off topic, and youre probably aware of this, but for anyone else interested, Jim Rech wrote some fairly slick code to allow a user to Select a Folder in Excel 2000. I've used it myself under permission from Jim in my own Apps.

Can be downloaded here -

http://www.oaltd.co.uk/MVP/Default.htm

(about half way down the page)

This bit of wizardry demonstrates how to call the Windows API to show the standard “browse for folder” dialog. The technique demonstrates how to use callback functions with Excel 97 and Excel 2000 in order to pre-select the initial folder. See the “Discussion” sheet in the workbook for a more detailed explanation. Version 2.0 adds support for the Windows 2000 “new style” browse dialog as well as for centring the dialog on screen.

Worth a look, if youre into that kind of thing ;-)

Ger