Macro selects folder
 

Hi,

I have a macro that selects a folder - the problem is that when it selects
the folder you cant see the files there - is there some where to fix this -
because they need to see where the file is in order to select the folder.

Here is the macro - Thanks for your help

Sub GetFolder2(Output)
Dim Output As String


' For Excel 2002
If Val(Application.Version) < 10 Then
MsgBox "This requires Excel 2002 or later.", vbCritical
Exit Sub
End If
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a location for the backup"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Canceled"
'Else
' MsgBox .SelectedItems(1)
End If
End With

Output =
Application.FileDialog(msoFileDialogFolderPicker). SelectedItems(1)

End Sub

Macro selects folder
 

If the user is selecting a file, why not use application.getopenfilename?

Or maybe you could use something like:

Option Explicit
Sub testme()
Dim myFolderName As String
Dim myFileName As Variant

myFileName = Application.GetSaveAsFilename _
(InitialFileName:="FilenameWillBeIgnored", _
filefilter:="Excel Files, *.xls")

If myFileName = False Then
Exit Sub
End If

myFolderName _
= Left(myFileName, InStrRev(myFileName, Application.PathSeparator) - 1)

MsgBox myFolderName

End Sub

InstrRev was added in xl2k. It won't work with xl97.

Jeff wrote:

Hi,

I have a macro that selects a folder - the problem is that when it selects
the folder you cant see the files there - is there some where to fix this -
because they need to see where the file is in order to select the folder.

Here is the macro - Thanks for your help

Sub GetFolder2(Output)
Dim Output As String


' For Excel 2002
If Val(Application.Version) < 10 Then
MsgBox "This requires Excel 2002 or later.", vbCritical
Exit Sub
End If
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a location for the backup"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Canceled"
'Else
' MsgBox .SelectedItems(1)
End If
End With

Output =
Application.FileDialog(msoFileDialogFolderPicker). SelectedItems(1)

End Sub

--

Dave Peterson