VBA Example - Select a Directory?

You don't need the On Error

Dim FolderName As String

With Application.FileDialog(msoFileDialogFolderPicker)

        .AllowMultiSelect = False

        If .Show = -1 Then

            FolderName = .SelectedItems(1)

        End If

    End With

--

HTH

Bob

<Rick Rothstein>; <MVP - Excel> wrote in message news:*** Email address is removed for privacy ***...

After you run the following code, the variable named FolderName will contain the full path of the selected folder (or the empty string "" if the user Cancels the dialog box)...

Dim FolderName As String

With Application.FileDialog(msoFileDialogFolderPicker)

  .AllowMultiSelect = False

  .Show

  On Error Resume Next

  FolderName = .SelectedItems(1)

  Err.Clear

  On Error GoTo 0

End With

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Thanks Rick,

That seems to be working quite well, although, I'll probably take xld's suggestion too.

Thanks everyone,

Linn

You don't need the On Error

Can you tell that was the first time I ever used that (or pretty much any) dialog box?<g> Thanks for pointing that out.

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

One way

I use it in this code example

http://www.rondebruin.nl/csv.htm

    Set oApp = CreateObject("Shell.Application")

   Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)

   If Not oFolder Is Nothing Then

Regards Ron de Bruin

http://www.rondebruin.nl/tips.htm

"lkubler" wrote in message news:*** Email address is removed for privacy ***...

Hi,

Using Excel 2003 sp3 and I'm trying to write a macro that processes all the workbooks in a specific folder.  I want the user to be able to select which folder to process but I don't know how to code that and I can't seem to find anything useful on-line.

Can someone point me in the right direction or give me a quick example? What I'd like is for the user to be able to navigate the directory structure and pick the desired folder and have the path stored in a variable for use in the application.filesearch.lookin property.

Thanks in advance,

Linn