Select folders/files with memory (Excel2003 VBA)

Topic

New Reply

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:

• Use Excel VBA-code to trigger a dialog box (could be a ‘standard’ Windows dialog called by API)
• Dialog should return array of selected files and/or folders
• Nice to have would be ablity for user to select files using Shift & Ctrl as standard in Windows
• 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
• 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
• 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)
• Allow dialog-box title to be set from code
• Allow initial/default drive&folder
• 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

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

  Erik Jan

Reply | Quote

Replies

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.

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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[/url] and parameters for the rootfolder (last argument) are found here: http://msdn.microsoft.com/library/default….erconstants.asp[/url]. 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.

Reply | Quote

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

Reply | Quote

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%5B/url%5D
No registration required.
Jim Cone
San Francisco, USA

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Look at the “IsAddin” property of the workbook object.

Jim Cone

Reply | Quote

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?

Reply | Quote

># 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.

Reply | Quote