I have a spreadsheet application that is hard coded to search a folder on our
network. With the click of a button, it loops through all the access
databases in this folder and performs record count queries on twelve tables
(with the same table names in each database-just the databases have different
names) and prints the database name, table and record count in excel. It
works great.

Now I would like the user to be able to click a button on the spreadsheet
and have the open dialogue box (or some other control where they can select
a FOLDER---NOT A FILE!!) pop up and have them select the folder and have
this path saved in a variable. Then i will insert the variable name where I
currently have the path hard coded. then they can click the start button as
usual.

I tried using Application.GetOpenFilename but that makes you select a file.
I need the user to be able to select a folder and I don't want the folder
opened. I just want the path saved in a variable and I'm not sure how to do
this.

Thanks


--
Billy Rogers

Dallas,TX

Currently Using Office 2000

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
MsgBox .SelectedItems(1)
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BillyRogers" wrote in message
...
I have a spreadsheet application that is hard coded to search a folder on
our
network. With the click of a button, it loops through all the access
databases in this folder and performs record count queries on twelve
tables
(with the same table names in each database-just the databases have
different
names) and prints the database name, table and record count in excel. It
works great.

Now I would like the user to be able to click a button on the spreadsheet
and have the open dialogue box (or some other control where they can
select
a FOLDER---NOT A FILE!!) pop up and have them select the folder and have
this path saved in a variable. Then i will insert the variable name where
I
currently have the path hard coded. then they can click the start button
as
usual.

I tried using Application.GetOpenFilename but that makes you select a
file.
I need the user to be able to select a folder and I don't want the folder
opened. I just want the path saved in a variable and I'm not sure how to
do
this.

Thanks


--
Billy Rogers

Dallas,TX

Currently Using Office 2000

I don't think that works in Excel 2000. We are supposed to be getting
Office 2003 next month though, so I'll be able to use it then.


--
Billy Rogers

Dallas,TX

Currently Using Office 2000


"Bob Phillips" wrote:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
MsgBox .SelectedItems(1)
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BillyRogers" wrote in message
...
I have a spreadsheet application that is hard coded to search a folder on
our
network. With the click of a button, it loops through all the access
databases in this folder and performs record count queries on twelve
tables
(with the same table names in each database-just the databases have
different
names) and prints the database name, table and record count in excel. It
works great.

Now I would like the user to be able to click a button on the spreadsheet
and have the open dialogue box (or some other control where they can
select
a FOLDER---NOT A FILE!!) pop up and have them select the folder and have
this path saved in a variable. Then i will insert the variable name where
I
currently have the path hard coded. then they can click the start button
as
usual.

I tried using Application.GetOpenFilename but that makes you select a
file.
I need the user to be able to select a folder and I don't want the folder
opened. I just want the path saved in a variable and I'm not sure how to
do
this.

Thanks


--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Okay, if you don't have 2002 or later try this


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 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.") As String
'-------------------------------------------------------------
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

BTW, hardly seems worth getting 2003. 2000 is not much different, and 2007
comes out next year.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BillyRogers" wrote in message
...
I don't think that works in Excel 2000. We are supposed to be getting
Office 2003 next month though, so I'll be able to use it then.


--
Billy Rogers

Dallas,TX

Currently Using Office 2000


"Bob Phillips" wrote:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
MsgBox .SelectedItems(1)
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BillyRogers" wrote in message
...
I have a spreadsheet application that is hard coded to search a folder
on
our
network. With the click of a button, it loops through all the access
databases in this folder and performs record count queries on twelve
tables
(with the same table names in each database-just the databases have
different
names) and prints the database name, table and record count in excel.
It
works great.

Now I would like the user to be able to click a button on the
spreadsheet
and have the open dialogue box (or some other control where they can
select
a FOLDER---NOT A FILE!!) pop up and have them select the folder and
have
this path saved in a variable. Then i will insert the variable name
where
I
currently have the path hard coded. then they can click the start
button
as
usual.

I tried using Application.GetOpenFilename but that makes you select a
file.
I need the user to be able to select a folder and I don't want the
folder
opened. I just want the path saved in a variable and I'm not sure how
to
do
this.

Thanks


--
Billy Rogers

Dallas,TX

Currently Using Office 2000