Get output Path

  • Thread starter Thread starter annapuvat
  • Start date Start date
A

annapuvat

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)
 
Bob,
Thanks. The code is a bit intimidating for a newbie, but works like
a charm ! Curious - why isn't there a built in function for this
similar to GetOpenFilename ?

Regards

Anna
 
Anna,

I believe that Excel 2000 has introduced such a dialog.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Yes it was Excel 2002 and not Excel 2000
You have got a good reply to this.... here is perhaps another
way to get this .... less intimidating :-)


Sub BrowseForFolder_Shell()
'//Minimum DLL version shell32.dll version 4.71 or later
'//Minimum operating systems Windows 2000, Windows NT 4.0 with
Internet Explorer 4.0,
'//Windows 98, Windows 95 with Internet Explorer 4.0
Dim objShell As Object
Dim objFolder As Object
Dim strFolderFullPath As String

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, "Please select a folder",
0, "C:\")

If (Not objFolder Is Nothing) Then
'// NB: If SpecFolder= 0 = Desktop then ....
On Error Resume Next
If IsError(objFolder.Items.Item.path) Then strFolderFullPath =
CStr(objFolder): GoTo GotIt
On Error GoTo 0
'// Is it the Root Dir?...if so change
If Len(objFolder.Items.Item.path) > 3 Then
strFolderFullPath = objFolder.Items.Item.path &
Application.PathSeparator
Else
strFolderFullPath = objFolder.Items.Item.path
End If
Else
MsgBox "User cancelled": GoTo Xit
End If

GotIt:
MsgBox "You selected:= " & strFolderFullPath, vbInformation,
"ObjectFolder:= " & objFolder

Xit:
Set objFolder = Nothing
Set objShell = Nothing

End Sub
 
That was what I meant, I have XL2000 on this machine, so I should know that
doesn't<G>


Bob
 
Folks, thank you all for detailed (and intimidating ;-) ) response.
I'm able to incorporate Bob Phillips' function into my program.
I sought help/samples for my program in an earlier post on 2/20/04 -
subject: Compare Worksheets. The program is to compare workbooks from
two user specified folders.I was able to use GetOpenFilename for
accepting one set of user-specified workbooks. Using Bob's GetFolder
function, I am able to get get the location of workbooks to be
compared. These workbooks have the same name as in the first folder. I
am looking to compare workbooks in the two folders as detailed in my
post of 2/20. Using a sample from Tom Ogilvy, I've progressed a bit,
using Application.Match to locate matching row between two workbooks.
I'm trying to figure out the command(s) that will compare individual
cells on a matched row and highlight difference.
For a newbie, my progress has been sure, but painfully slow. This
group is a great resource. Thanks again, and in anticipation for all
your help

-Anna
 
Hi Anna,

Good news all round.

We'll see you with the next round<g>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
I've completed my code and tested successfully. Funny now, it took
more words to describe the problem than the actual code :) Viva Excel
& VBA !

Thanks. Your GetFolder routine was imo the piece that I needed help
with, the most.

Regards

Anna
 
Back
Top