Prompt user for folder or path 1

[yippiekyyay]

With help from this forum, I’ve written a macro which will create separate documents for each record in a mail merge. I’ve specified the folder where to place the files (ChangeFileOpenDirectory "C:\mail_merged_files_folder\"), but I’d rather a window open up that asks the user to specify a folder of their choice.

Basically, I’m going for something like this:

Dim myFolderPath As String
myFolderPath = ???
ChangeFileOpenDirectory myFolderPath

Thanks in advance!

 

[SkipVought]

Hi,

What application?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[PHV]

In any VBA application you may use this function:

Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
  PickFolder = f.Items.Item.path
End If
Set f = Nothing
Set SA = Nothing
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

Looks like Word. Can you not just use an Inputbox?

faq219-2884

Gerry
My paintings and sculpture

 

[combo]

Reference shell32.dll:

Dim fName As String
Dim shellApp As Shell32.Shell
Set shellApp = New Shell32.Shell
fName = shellApp.BrowseForFolder(0, "Please choose/create a folder", 0).Title
' (last arg. 512 instead of 0 to disable new folder)
MsgBox fName


combo

 

[combo]

For full path in above code replace 'Title' by 'Self.Path'

combo

 

[yippiekyyay]

Thank you all very much!

Yes it was for Word and PHV's code worked great (I'll try yours as well in a minute combo).

Thanks again!