Filedialog equivalent in office 9 (Office 9)

Topic

New Reply

I know this is probably a stupid question, but I am trying to deploy a macro written (and working!) in Excel 10 on a Office 2000 machine. The macro simply displays a dialog to allow choice of a folder (Folder picker in FileDialog), then itereates through all the images in the file inserting them into the spreadsheet together with some text etc. Works fine on the Office XP machines, but FileDialog seems to be in the Office 10 library, and I can’t find any simple way of doing the same thing.

Reply | Quote

Replies

(Edited by HansV on 02-Jul-03 14:19. Had left some Access-specific code. Also see Pieterse’s reply in this thread.)

You can put the following code in a standard module:

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

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 Const BIF_RETURNONLYFSDIRS = &H1

Public Function BrowseFolder(szDialogTitle As String) As String
Dim bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

With bi
.hOwner = 0 ‘ had hWndAccessApp here, which is for Access only
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)

If SHGetPathFromIDList(ByVal dwIList, ByVal szPath) Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos – 1)
End If
End Function

You can let the user pick a folder as follows:

Dim strFolder As String
strFolder = BrowseFolder(“Select a folder”)

If the user clicked Cancel, strFolder will be the empty string “”.

Reply | Quote

And to make this work in Excel:

Option Explicit

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

Private Declare Function FindWindow32 Lib “user32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

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 Const BIF_RETURNONLYFSDIRS = &H1

Public Function BrowseFolder(szDialogTitle As String) As String
Dim bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer
Dim hwndXL As Long
hwndXL = FindWindow32(“XLMAIN”, Application.Caption)
‘ For Word:
‘ hwndXL = FindWindow32(“OPUSAPP”, Application.Caption)
‘ Of course hwndXL looks funy in Word
With bi
.hOwner = hwndXL
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)

If SHGetPathFromIDList(ByVal dwIList, ByVal szPath) Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos – 1)
End If
End Function

Sub example()
Dim strFolder As String
strFolder = BrowseFolder(“Select a folder”)

End Sub

Reply | Quote

Thanks, I always forget to check these little things. I will adapt my reply.

Reply | Quote

I tend to make the same mistakes…

Which is why it is a good thing people read other peoples’ posts.

Reply | Quote

Of course setting the .Owner to zero does not tie the dialog to Excel, so one can switch applications, come back, OK the dialog and not be returned to Excel (Word).

I prefer my solution in getting the hwnd for Excel (Word) and using that.

Reply | Quote

Agreed. That’s why I referred to your post in the header.

Reply | Quote

Better still, use GetActiveWindow instead of FindWindow. Works with any calling app.

Private Declare Function GetActiveWindow Lib “user32” () As Long

hWnd = GetActiveWindow()
With bi
.hOwner = hWnd
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With
etc..

Reply | Quote

Thanks both of you – leads to 4 conclusions 1) I see why they put FileDialog into 10, 2) I don’t see why it took them until Office 10!, 3) I feel MUCH better about not being able to figure out how to do it, and 4) there are some really generous people in the lounge with a lot of knowledge they willing to share.
I have a feeling that reading this code will advance my knowledge of VBA significantly (and believe me it needs that!)
Thanks again.

Reply | Quote

In stead of all those API functions you can use the following mini code:

Sub Test()
Debug.Print GetFolder(Title:=”Gimme a Folder”, RootFolder:=&H11)
End Sub

Function GetFolder(Optional Title As String = “Select a Folder”, Optional RootFolder As Variant) As String
On Error Resume Next
GetFolder = CreateObject(“Shell.Application”).BrowseForFolder(0, Title, 0, RootFolder).Items.Item.Path
End Function

Reply | Quote

That is a good one!

Reply | Quote

The above Requires
shell32.dll version 4.71 or later
Minimum operating systems: Windows

Reply | Quote

How about a dialog box to select a file instead of just the folder?

..dane

Reply | Quote

Which API are you using?

Andrew Cronnolly’s post 179268 shows how, if you are using BrowseForFolder, you can trick it into letting you pick files. The example is in VBScript, but the principle should apply equally well to other contexts.

Reply | Quote

Andrew also states that it is kind of unpredictable on some platforms (XP, for one). Since this potentially will be used by others in my location once it’s operational, I don’t have much of a control over what OS version it’s running on. But we do all have Office 2000 (v9)…

thanks,
..dane

Reply | Quote

Yes, he does, but I suspect that has more to do with the backdoor way of invoking the Shell object in VBScript than with the API command itself. Testing will be needed to prove or disprove the hypothesis.

Reply | Quote

Yes, he does, but I suspect that has more to do with the backdoor way of invoking the Shell object in VBScript than with the API command itself. Testing will be needed to prove or disprove the hypothesis.

Reply | Quote

Andrew also states that it is kind of unpredictable on some platforms (XP, for one). Since this potentially will be used by others in my location once it’s operational, I don’t have much of a control over what OS version it’s running on. But we do all have Office 2000 (v9)…

thanks,
..dane

Reply | Quote

Which API are you using?

Andrew Cronnolly’s post 179268 shows how, if you are using BrowseForFolder, you can trick it into letting you pick files. The example is in VBScript, but the principle should apply equally well to other contexts.

Reply | Quote

(Edited by JohnBF on 01-Jul-04 11:52. Outlook doesn’t support the Method. Available only in Ofiice 10+.)

Funny you should ask. This works in Excel but won’t run in Outlook, because the App doesn’t support the FileDialog method.

Sub TestBrowse4file()
MsgBox Browse4File
End Sub

Private Function Browse4File() As String
Dim dlgOpen As FileDialog
Dim varFile As Variant
Dim strReturnedPath As String

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False
.Show
For Each varFile In .SelectedItems
strReturnedPath = varFile
Next varFile
End With
Browse4File = strReturnedPath & CStr(varFile)

End Function

I’m sure it needs more work.

Reply | Quote

Bummer it’s only OFF v10+… I’ve got v9. That would have been nice!

Reply | Quote

(Edited by JohnBF on 01-Jul-04 14:11. )

Try the code here, but edit this line:

.lpstrFilter = “Image Files” + Chr(0) + “*.bmp;*.jpg;*.jpeg;*.jpe” + _
Chr(0) + “All Files (*.*)” + Chr(0) + “*.*” + Chr(0) + Chr(0)

according to the target file type, such as all:

.lpstrFilter = “All Files (*.*)” + Chr(0) + “*.*” + Chr(0) + Chr(0)

Edit. And comment out

Public Const OFN_ALLOWMULTISELECT = &H200&

If you want the user to only be able to select a single file.

Reply | Quote

(Edited by JohnBF on 01-Jul-04 14:11. )

Try the code here, but edit this line:

.lpstrFilter = “Image Files” + Chr(0) + “*.bmp;*.jpg;*.jpeg;*.jpe” + _
Chr(0) + “All Files (*.*)” + Chr(0) + “*.*” + Chr(0) + Chr(0)

according to the target file type, such as all:

.lpstrFilter = “All Files (*.*)” + Chr(0) + “*.*” + Chr(0) + Chr(0)

Edit. And comment out

Public Const OFN_ALLOWMULTISELECT = &H200&

If you want the user to only be able to select a single file.

Reply | Quote

Bummer it’s only OFF v10+… I’ve got v9. That would have been nice!

Reply | Quote

(Edited by JohnBF on 01-Jul-04 11:52. Outlook doesn’t support the Method. Available only in Ofiice 10+.)

Funny you should ask. This works in Excel but won’t run in Outlook, because the App doesn’t support the FileDialog method.

Sub TestBrowse4file()
MsgBox Browse4File
End Sub

Private Function Browse4File() As String
Dim dlgOpen As FileDialog
Dim varFile As Variant
Dim strReturnedPath As String

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False
.Show
For Each varFile In .SelectedItems
strReturnedPath = varFile
Next varFile
End With
Browse4File = strReturnedPath & CStr(varFile)

End Function

I’m sure it needs more work.

Reply | Quote

Word has Dialogs(wdDialogFileOpen) (use .Display to retrieve a filename without opening it, .Show to open a file)
Excel has Application.GetOpenFilename to retrieve a filename without opening it, and Application.Dialogs(xlDialogOpen).Show to open a file.

Reply | Quote

WOW! So simple! I love it when it’s simple.

Now, just gotta parse the string out to the drive:pathfilename.extension…

Hans, I konw the answer to my question is “many years of experience,” so instead I’ll ask another; what would you recommend as a few (if there is not just one) of the best VBA reference books out there? This forum is so wonderful, but I feel like some of my questions are so simple I need a good reference to check before asking here.

thanks so much,
..dane

Reply | Quote

Perhaps others can answer your question about books (or do a search in this forum and the Word and Excel forums.) I don’t use books very often, I mostly rely on the online help, the object browser (F2 in the Visual Basic Editor) and on Internet (the Lounge, newsgroups, Knowledge Base, …)

Reply | Quote

Perhaps others can answer your question about books (or do a search in this forum and the Word and Excel forums.) I don’t use books very often, I mostly rely on the online help, the object browser (F2 in the Visual Basic Editor) and on Internet (the Lounge, newsgroups, Knowledge Base, …)

Reply | Quote

WOW! So simple! I love it when it’s simple.

Now, just gotta parse the string out to the drive:pathfilename.extension…

Hans, I konw the answer to my question is “many years of experience,” so instead I’ll ask another; what would you recommend as a few (if there is not just one) of the best VBA reference books out there? This forum is so wonderful, but I feel like some of my questions are so simple I need a good reference to check before asking here.

thanks so much,
..dane

Reply | Quote

Hans,

[indent]

---

Word has Dialogs(wdDialogFileOpen) (use .Display to retrieve a filename without opening it, .Show to open a file)
Excel has Application.GetOpenFilename to retrieve a filename without opening it, and Application.Dialogs(xlDialogOpen).Show to open a file.

---

[/indent]

This method doesn’t seem to support a “default folder” to start out in… any way to do that?

thanks!
..dane

Reply | Quote

Excel

You can use the old ChDrive and ChDir instructions to set the start folder:

Dim varResult As Variant
ChDrive “C”
ChDir “C:ExcelTest”
varResult = Application.GetOpenFilename
If varResult = False Then
MsgBox “No file selected.”
Else
MsgBox “You selected ” & varResult
End If

or

ChDrive “C”
ChDir “C:ExcelTest”
Application.Dialogs(xlDialogOpen).Show

Word

With Application.Dialogs(wdDialogFileOpen)
.Name = “C:WordTest*.*”
If .Display = False Then
MsgBox “No file selected.”
Else
MsgBox “You selected ” & .Name
End If
End With

Reply | Quote

In Word (but probably not in Excel), take a look at the following:

ChangeFileOpenDirectory Path:=”c:testing”
Display dialog here
strChosenPath = Options.DefaultFilePath(wdCurrentFolderPath)

That’s somewhat from memory, so no warranties given.

Reply | Quote

In Word (but probably not in Excel), take a look at the following:

ChangeFileOpenDirectory Path:=”c:testing”
Display dialog here
strChosenPath = Options.DefaultFilePath(wdCurrentFolderPath)

That’s somewhat from memory, so no warranties given.

Reply | Quote

Hans,

[indent]

---

Word has Dialogs(wdDialogFileOpen) (use .Display to retrieve a filename without opening it, .Show to open a file)
Excel has Application.GetOpenFilename to retrieve a filename without opening it, and Application.Dialogs(xlDialogOpen).Show to open a file.

---

[/indent]

This method doesn’t seem to support a “default folder” to start out in… any way to do that?

thanks!
..dane

Reply | Quote

Word has Dialogs(wdDialogFileOpen) (use .Display to retrieve a filename without opening it, .Show to open a file)
Excel has Application.GetOpenFilename to retrieve a filename without opening it, and Application.Dialogs(xlDialogOpen).Show to open a file.

Reply | Quote

How about a dialog box to select a file instead of just the folder?

..dane

Reply | Quote