Hi All,
Self explanatory Title
I hope it's useful to atleast some of you !
Note: Change the path before clicking the button.
Hi All,
Self explanatory Title
I hope it's useful to atleast some of you !
Note: Change the path before clicking the button.
Re: Listing of Files and Worksheets
Thanks, Kris.
Re: Listing of Files and Worksheets
Nice work Kris... thats a keeper.
Re: Listing of Files and Worksheets
Hi,
Thanks Dave, Ger.
Re: Listing of Files and Worksheets
Wow it works good. Even shows the names of the sheets not visible also.
Simply amazing Kris :drum:
Re: Listing of Files and Worksheets
Hi,
Thanks David :cheers:
Re: Listing of Files and Worksheets
A modified version.
Re: Listing of Files and Worksheets
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
Re: Listing of Files and Worksheets
Hi Dave,
That won't work in 2000 version.
QuoteAnyway, 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.
Re: Listing of Files and Worksheets
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)
Quote
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
Re: Listing of Files and Worksheets
Hi Ger,
Thanks for that. I also had Chip's code, but that need some API calls. I don't want to include all that stuff into this.
Here is version 3. This reduces the time of storing all the sheet names when userform intializing.
Dave, now which version you are going to include in the news letter?
Re: Listing of Files and Worksheets
Well, I wont include any API as I may get questions I have no answer for
I might just use a mix and include some of my own code.
Re: Listing of Files and Worksheets
A small change in the code to incluse 2007+ workbooks as well. Thanks Pike for pointing out this
In the Userform Initialize event, replace the line
[vb]fName = Dir(FilePath & "*.xls")[/vb]
with
[vb]fName = Dir(FilePath & "*.xls*")[/vb]
What if the workbooks are that I want to include is in multiple different folders. How would one adapt your code?
Don’t have an account yet? Register yourself now and be a part of our community!