Get output Path
 

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

Get output Path
 

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

Get output Path
 

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)

"annapuvat" wrote in message
om...
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

"Bob Phillips" wrote in message
...
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 . . . .

Get output Path
 

I think it was xl2002 that added application.filedialog.



Bob Phillips wrote:

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)

"annapuvat" wrote in message
om...
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

"Bob Phillips" wrote in message
...
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 . . . .

--

Dave Peterson

Get output Path
 

yes, it was in Office XP that it was added.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I think it was xl2002 that added application.filedialog.



Bob Phillips wrote:

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)

"annapuvat" wrote in message
om...
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

"Bob Phillips" wrote in message
...
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 . . . .

--

Dave Peterson

Get output Path
 

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


Dave Peterson wrote in message ...
I think it was xl2002 that added application.filedialog.



Bob Phillips wrote:

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)

"annapuvat" wrote in message
om...
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

"Bob Phillips" wrote in message
...
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 . . . .

Get output Path
 

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


Bob

"Dave Peterson" wrote in message
...
I think it was xl2002 that added application.filedialog.



Bob Phillips wrote:

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)

"annapuvat" wrote in message
om...
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

"Bob Phillips" wrote in message
...
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 . . . .

--

Dave Peterson

Get output Path
 

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

Get output Path
 

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)

"annapuvat" wrote in message
om...
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

Get output Path
 

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

"Bob Phillips" wrote in message ...
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)

"annapuvat" wrote in message
om...
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