Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select folders/files with memory (Excel2003 VBA)

    In the past I've been using several implementations of VBA-code that allowed me to select files and/or directories using a dialog-box. I've never stumbled on one that really gives me all that I would need yet (maybe I didn't look in the right places?).

    Recently Chip Pearson put some nice examples (+ code) on his great website (http://www.cpearson.com/excel.htm) but also there I'm still missing what I think I need. Still, I'd say this should not be all that difficult, let me try to describe:

    <UL><LI>Use Excel VBA-code to trigger a dialog box (could be a 'standard' Windows dialog called by API)
    <LI> Dialog should return array of selected files and/or folders
    <LI>Nice to have would be ablity for user to select files using Shift & Ctrl as standard in Windows
    <LI>Nice to have would be possibility to preset what may be selected: one file, more files, only files, only one folder, more folders, files and folders
    <LI>Nice to have would be possibility to select that i.s.o. a selected folder, the code returns all files in that folder (and subfolders?). And therefore, if files and folders are selected, it would return all files
    <LI> Tool should return path and file-name either by presetting what is required or e.g. the path in column 1 of the array, the filename in column2 and the extension in column 3 (maybe this can even be extended to include more info like attributes, filedate, filetime, filesize)
    <LI>Allow dialog-box title to be set from code
    <LI> Allow initial/default drive&folder
    <LI>Also return or remember selected directory such that on a next call to the routine, this could be set as initial drive&folder [/list]Oh... of course the basic stuff would already be great, the nice-to-haves etc. is my imagination running wild <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Finally, given some "basis-code", maybe I'll tweak things myself and will post the results back here.

    Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    The Application.FileDialog object does part of what you want (it has folder picker and multi-select file open functionality), but I don't know of a single tool that does all. You'll have to create it yourself, for example by displaying the FileDialog repeatedly, and keeping track of the selected items.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    This comes close to a couple of the things you mentioned, all it does not do is "all files" and "select folders":

    <pre>Sub GetOpenFileNameExample3()
    Dim lCount As Long
    Dim vFilename As Variant
    Dim sPath As String
    Dim lFilecount As Long
    sPath = "c:windowstemp"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , _
    "Please select the file(s) to open", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = 1 To UBound(vFilename)
    MsgBox vFilename(lCount)
    Next
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    A couple of other things which might be helpful (most of which I learned here in the Lounge).

    You can keep the last used path in the registry with code like this:
    ' At some point after the path is selected and set in "strCurrPath"
    SaveSetting AppName:="mySub", Section:="Options", Key:="UseLastPath", Setting:=strCurrPath
    ' Initialization of the sub:
    strLastPath = CStr(GetSetting(AppName:="mySub", Section:="Options", Key:="UseLastpath", Default:="False"))

    As an alternative to the built in File Selection dialogs, this path selection dialog may be useful:
    CreateObject("Shell.Application").BrowseForFolder( 0, "Header text", 0, 0).items.Item.Path

    The BrowseForFolder Method is documented here: http://msdn.microsoft.com/library/default....seforfolder.asp and parameters for the rootfolder (last argument) are found here: http://msdn.microsoft.com/library/default....erconstants.asp. The dialog returned is a little different on Windows 2000 than XP. I don't have Vista, so don't know if there are any related Vista issues.

    Unlike most MS Apps, Outlook doesn't have a general user need to select files, so it doesn't have built-in File Selection dialogs, so FWIW I use the attached API (plus two subs to illustrate its use) for some VBA file name things I wanted to do in Outlook.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Not everything, but close to what you are after...
    The free Excel add-in "List Files"
    Also does folders.
    Download from... http://www.realezsites.com/bus/primitivesoftware
    No registration required.
    Jim Cone
    San Francisco, USA

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Thanks, I had used this one before but as I indicated, I was/am looking for one approach that does all. Will look further to other suggestions

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Thanks, I cannot test this one. It loads in my Excel but shows no menu anywhere - sorry

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Also this one looks nice but browses for a folder only - I'll see if I can find some time to combine possibilities somehow

  9. #9
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    A "List Files" menu item should appear on the Tools menu (except for xl2007 users).
    You could try unchecking it in Tools | Add-ins, ok your way out, then try checking it again.
    What operating system/language are you using?
    Jim Cone
    San Francisco, USA

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Nope... it asks if I want to have Macro enabled and I say yes. No menu entries under tools.

    Under Add-ins, I don't see it. It's even more strange... I see it loaded in the VBA screen but still I can load it again ("Macros Enabled?", YES) and again, and... Still nothing in the tools menu. XP Pro / US English / Office 2003

    Erik Jan

  11. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    It sounds as if you tried to open the file instead of installing it as an add-in.
    You probably should close Excel and try the instructions below that
    are in the Read Me file included with the add-in...

    Installing:
    Microsoft Excel 97 (or newer) is required.
    Detach or copy "List Files.xla" to your hard drive

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Interesting... I created quite some XLA's myself and these can always be used either as add-in (as you describe) but also simply by opening the file directly.

    Indeed, your suggestion below to load it as addin helped and now things work. Strange that it did/does not work upon normal load; did you intentionally put code in your program to prevent that? Why?

    Anyway; this works but I'm still continuing my search to the ideal VBA code to allow me to select and load files & directories as described in my initial post in this thread. I got some inspiration from all the answers I received and will see if I can create something. Once I do, I'll report back and post my code.

    EJ

  13. #13
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    re: "did you intentionally put code in your program to prevent that?"

    Look at the "IsAddin" property of the workbook object.

    Jim Cone

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VBA)

    Ah, yes... I remember. Of course now my remaining question is (I'm trying to understand and learn): why would you like this tool to only run as add-in?

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select folders/files with memory (Excel2003 VB

    ># Also return or remember selected directory such that on a next call to the routine, this could be set as initial drive&folder
    I just stumbled acros this thread while seraching for something else.
    I wrote MRUse for Word2000+ and could migrate it to excel (although it uses the Excel MRUse within Word).
    You can d/l the latest release from my downloads page and test-drive it. If you see features you like I could paste the code here.
    The recent fix was to use the path of the most recently used file and set it as the Document Path in Word, ready for the next File, Open command.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •