I want to call the common dialog to open a file, so I don’t have to write my own. I’ve played around with it for a while, but haven’t managed to get it to work. And my Windows API book is at home. and google hasn’t been much help so far.
Does anyone have the proper syntax for loading / calling the common dialog box for opening a file handy? I would sure appreciate it… or if there is another way to do this that I haven’t figured out, that’s fine as well. Thanks.
What about this link regarding the GetOpenFileName() function?
Is this what you’re looking for?
Here’s an example:
fileToOpen = Application _
.GetOpenFilename(“Text Files (*.txt), *.txt”)
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If
What sort of file are you trying to open (*.rep, other)? Are you actually trying to open a file, or just get the name / path of a file? Is the VBA being run from Business Objects, or from another app like Excel?
Yes, that’s a start. But what I also need is the code to tell BusinessObjects to reference the common dialog dll. You need to declare the external function before you can use it.
In BusinessObjects VBA the Application object doesn’t support this method. So in order to use it, you have to declare an external reference to the Windows dll. Then you can call it.
For example, the following is from an old program I wrote that needed to wait on an external event. Rather than use some “busy” logic that ties up clock cycles and cpu resources, I called the windows “Sleep” function.
' Need to use the sleep() function from the windows API
Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Once you declare the function you can call it like this:
Call Sleep (30000)
In MS Excel, you can call a method GetOpenFilename() directly. But BusinessObjects doesn’t have it. At least as far as I can tell.
Actually, that is it exactly. I would like the user to be able to “browse” and select a directory path. That path provides the source for the location that I want to process.
'******************************************************************************
' Utility provided as is (no warranty implied). All rights reserved by author.
' Module: BrowseForDirectory user module
' Purpose: Allow user to choose a directory using the browse dialogue box. VBA
' does not provide the functionality directly, so use Windows API.
' Change Log:
' 10-Feb-2003, Dwayne Hoffpauir
' Initial release. Code taken from Microsoft Excel 2000 Power
' Programming with VBA, by John Walkenbach
'******************************************************************************
Option Private Module 'prevent contents of this module from being visible to Business Objects
Option Explicit 'require variables to be declared before being used
'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Public 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 GetDirectory(Optional Msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer
' Root folder = Desktop
bInfo.pidlRoot = 0&
' Title in the dialog
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = Msg
End If
' Type of directory to return
bInfo.ulFlags = &H1
' Display the dialog
x = SHBrowseForFolder(bInfo)
' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function
Then something like DirString = GetDirectory(“Put meaningful instructions here”) to use it.
I’m afraid I don’t. I seem to remember options other than 0&, but the list was rather sparse, and in any case didn’t allow a “user defined” option. I think this method uses a “least common denominator” approach. That is, most compatible with the various versions of Windows. I read somewhere that there are “better” methods in more recent (2K/XP) versions of Windows, but I didn’t do much research on them.
Ok, I might be missing something but wasnt that about the hardest way to get a file to open?? I just did a little quick test and did it with this code, well, I didn’t open the file, but I sure got the file path.
Private Sub Document_BeforeSave(Cancel As Boolean)
form.test.ShowOpen
MsgBox form.test.FileName
End Sub
I just stuck it in the before save event for testing. Anyway, I just added a form, then right clicked in the tool box and added component, then added the common dialog box. I inserted a form and put the box on it. About all you really need to do. Then in my code I just reference that dialog box, you can make it do anything you want from there on out. There are some more options you can add to the box too, like default directory, open printers, stuff like that. Seems like a much easier way to open a file to me, but I am not an API expert either, I try to stay away from them unless I have to use them.
The main issue is that I don’t want to open a file, I want a path. Dwayne’s code works perfectly for that.
I didn’t know about the ability to add a control to the toolbox… but even if I go that route, I don’t seem to be able to find the common dialog. What is the name of the control I should add in? I would like to know that for the future.
The calendar control looks very cool, to say the least. 8)
The code I sent does just that, it gets you the path. That is all the dialog box really does, what you do after that is up to you.
The name of the control is Microsoft common dialog control. It may not be installed on your pc. The name of the file is Comdlg32.ocx. If you dont have it, I am pretty sure you can download it from support.microsoft.com for free. It comes with VB but I dont think it is an exclusive or anything.
Yup, just want the folder. I don’t want the user to have to click on a file. I think I’ll stick with the code Dwayne posted. Mainly because it’s working, and it does what I want it to do. 8)
I think this is the main reason I went with the less fancy, but “foolproof” solution, even though it involved an API call. With multiple versions of the comdlg32.ocx out there, with varying capabilities, and figuring out “if” it was installed on a given machine, was too much risk.
I tried out your code, very very slick. I like it.
There is one other way, but it still involves .ocx’s and you would have to make sure users had them. The VB common tool box ocx, I think it is mscomctl.ocx. This contains a bunch of controls that include the dir list box and the file list box. These together can give you the path of folder browsed to.
and google hasn’t been much help so far.
(BOB member since 2002-06-06)
(BOB member since 2002-09-19)
