用vba选择文件夹

pzhqh

Excel’s VBA 模块允许你用GetOpenFilename 和 GetSaveAsFilename 方法显示标准的文件打开和文件保存对话框。但是，现在并不是要大家做一个文件夹选择对话框。本篇介绍两种显示文件选择对话框的方法给大家。
第一种方法是使用Windows API 调用显示对话框。第二种方法用“Microsoft shell 控件和自动化 ”对象库。第一种方法可以在Windows的任意版本使用（win95或更晚），而第二种方法需要用户安装了Internet Explorer 5或更高版本。 使用 Windows API 函数 复制下列代码到标准代码模块： Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long

Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long


Function BrowseFolder(Optional Caption As String = "") As String

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With

FolderName = String$(MAX_PATH, vbNullChar)

ID = SHBrowseForFolderA(BrowseInfo)

If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If

End Function



你可以在下列代码中调用BrowseFolder函数

Dim FName As String
FName = BrowseFolder("Select A Folder")
If FName = "" Then
MsgBox "You didn’t select a folder"
Else
MsgBox "You selected: " & FName
End If


注意：API不允许你指定开始文件夹。 使用Shell控件库 首先你需要调用"Microsoft Shell Controls And Automation"对象库。在VBA编辑器中，选中“工具”项，点击选择“引用”，然后拖动滚动条选中并进行下一步。 然后复制下列代码到标准代码模块：

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260


Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String

Dim SH As Shell32.Shell
Dim F As Shell32.Folder

Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, _
InitialFolder)

If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
End If

End Function


你可以在下列代码中调用BrowseFolder函数

Dim FName As String
FName = BrowseFolder("Select a folder", "C:\InitialFolder")
If FName = "" Then
MsgBox "You didn’t select a folder"
Else
MsgBox "You selected: " & FName
End If


注意： Shell32 允许你指定开始文件夹。

Rowen

试了不错,是个好方法.

pwkduke

楼主，我试了一下你的程序，不错，但是如果将 FName = BrowseFolder("Select a folder", "C:\InitialFolder")
中的c:\initialfolder改成自己机子内的一个存在的文件夹后，弹出的对话框内就只有该文件夹下一级的目录，找不到C盘、我的电脑、我的文档之类的东西了，是你的程序有问题，还是ie的版本有问题，我是在98下装的是ie6。

pzhqh

以下是引用pwkduke在2004-4-1 14:54:00的发言： 楼主，我试了一下你的程序，不错，但是如果将 FName = BrowseFolder("Select a folder", "C:\InitialFolder") 中的c:\initialfolder改成自己机子内的一个存在的文件夹后，弹出的对话框内就只有该文件夹下一级的目录，找不到C盘、我的电脑、我的文档之类的东西了，是你的程序有问题，还是ie的版本有问题，我是在98下装的是ie6。

对API函数进行了解后，应该能解决

tzgwh

要是能再进一步到选择某个文件就好了。

cheese

我是初学者，能贴上附件，也好学习学习

welcpa

以下是引用cheese在2005-8-5 19:16:00的发言： 我是初学者，能贴上附件，也好学习学习

[em49]waiting[em48]

zhyzhsh

这么复杂的代码，我还是自己做个窗体得了。[em12]

第三杯茶

不错,感谢分享

nawong

QUOTE:

以下是引用zhyzhsh在2006-7-10 6:30:00的发言：
这么复杂的代码，我还是自己做个窗体得了。[em12]

拿來主義，拿來能用，不也是一種捷徑嗎？