Get output Path

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

Bob Phillips

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)
 
A

annapuvat

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
 
I

Ivan F Moala

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
 
B

Bob Phillips

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


Bob
 
A

annapuvat

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
 
B

Bob Phillips

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)
 
A

annapuvat

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
 
Top