In VBA how would I accept an output path. In my macro, would like the
user to specify the output location to write a new workbook to. I've
looked at GetOPenFilename samples, but that method requires users to
select a file before I can extract the path string.

Thanks in advance . . . .

Anna (hope that I have got that right)

Here is a little function that you can use. Put it in a standard code
module.

To invoke it, you just run

myFolder = GetFolder()

This will return your selected folder, or blank if you cancel..


Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

'---------------------------------------------------------------------------
----
' Private UDTs
'---------------------------------------------------------------------------
----
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'---------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a folder.")
'---------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"annapuvat" wrote in message
om...
In VBA how would I accept an output path. In my macro, would like the
user to specify the output location to write a new workbook to. I've
looked at GetOPenFilename samples, but that method requires users to
select a file before I can extract the path string.

Thanks in advance . . . .