GetOpenFilename
John Travis Beavers
Does anybody know how to open a file dialog box from a script in VBA under
Access? In Excel I would have used getOpenFilename but there doesn't
appear to be any such function for Access as far as I can tell. (Why
isn't it consistent?)
Thanks,
John Travis Beavers
-"Feel fit beatnik man out to make it rich..."
Jeff Jackson
Good Day John. In Access 2.0 where I am presently developing. Put the
following VBA code in a module:
Main Declarations are as follows:
Option Explicit
' Declare a user-defined type called "tagOPENFILENAME"
Type tagOPENFILENAME
lStructSize As Long 'Specifies the length, in bytes, of the
structure.
hwndOwner As Integer 'Identifies the window that owns the dialog
box.
hInstance As Integer
lpstrFilter As Long
lpstrCustomFilter As Long
nMaxCustFilter As Long
NFilterIndex As Long
lpstrFile As Long
nMaxFile As Long
lpstrFileTitle As Long
nMaxFileTitle As Long
lpstrInitialDir As Long
lpstrTitle As Long
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As Long
End Type
Declare Function GetOpenFileName% Lib "COMMDLG.DLL" (OPENFILENAME As
tagOPENFILENAME)
Declare Function GetSaveFileName% Lib "COMMDLG.DLL" (OPENFILENAME As
tagOPENFILENAME)
Declare Function lstrcpy& Lib "Kernel" (ByVal lpDestString As Any, ByVal
lpSourceString As Any)
Dim OPENFILENAME As tagOPENFILENAME
Global Const OFN_READONLY = &H1
Global Const OFN_OVERWRITEPROMPT = &H2
Global Const OFN_HIDEREADONLY = &H4
Global Const OFN_NOCHANGEDIR = &H8
Global Const OFN_SHOWHELP = &H10
Global Const OFN_ENABLEHOOK = &H20
Global Const OFN_ENABLETEMPLATE = &H40
Global Const OFN_ENABLETEMPLATEHANDLE = &H80
Global Const OFN_NOVALIDATE = &H100
Global Const OFN_ALLOWMULTISELECT = &H200
Global Const OFN_EXTENSIONDIFFERENT = &H400
Global Const OFN_PATHMUSTEXIST = &H800
Global Const OFN_FILEMUSTEXIST = &H1000
Global Const OFN_CREATEPROMPT = &H2000
Global Const OFN_SHAREAWARE = &H4000
Global Const OFN_NOREADONLYRETURN = &H8000
Global Const OFN_NOTESTFILECREATE = &H10000
Global Const OFN_SHAREFALLTHROUGH = 2
Global Const OFN_SHARENOWARN = 1
Global Const OFN_SHAREWARN = 0
'-------------------------------------------------------
' Open Common Dialog Function
'-------------------------------------------------------
Function OpenCommDlg ()
Dim Message$, Filter$, FileName$, FileTitle$, DefExt$
Dim Title$, szCurDir$, APIResults%
'*Define the filter string and allocate space in the "c" string
Filter$ = "Access(*.mdb)" & Chr$(0) & "*.MDB;*.MDA" & Chr$(0)
Filter$ = Filter$ & "Text(*.txt)" & Chr$(0) & "*.TXT" & Chr$(0)
Filter$ = Filter$ & "Batch(*.bat)" & Chr$(0) & "*.BAT" & Chr$(0)
Filter$ = Filter$ & Chr$(0)
'* Allocate string space for the returned strings.
FileName$ = Chr$(0) & Space$(255) & Chr$(0)
FileTitle$ = Space$(255) & Chr$(0)
'* Give the dialog a caption title.
Title$ = "My File Open Dialog" & Chr$(0)
'* If the user does not specify an extension, append TXT.
DefExt$ = "TXT" & Chr$(0)
'* Set up the default directory
szCurDir$ = CurDir$ & Chr$(0)
'* Set up the data structure before you call the GetOpenFileName
OPENFILENAME.lStructSize = Len(OPENFILENAME)
'If the OpenFile Dialog box is linked to a form use this line.
'It will pass the forms window handle.
'OPENFILENAME.hwndOwner = Screen.ActiveForm.hWnd
'If the OpenFile Dialog box is not linked to any form use this line.
'It will pass a null pointer.
OPENFILENAME.hwndOwner = 0&
OPENFILENAME.lpstrFilter = lstrcpy(Filter$, Filter$)
OPENFILENAME.nFilterIndex = 1
OPENFILENAME.lpstrFile = lstrcpy(FileName$, FileName$)
OPENFILENAME.nMaxFile = Len(FileName$)
OPENFILENAME.lpstrFileTitle = lstrcpy(FileTitle$, FileTitle$)
OPENFILENAME.nMaxFileTitle = Len(FileTitle$)
OPENFILENAME.lpstrTitle = lstrcpy(Title$, Title$)
OPENFILENAME.Flags = OFN_FILEMUSTEXIST Or OFN_READONLY
OPENFILENAME.lpstrDefExt = lstrcpy(DefExt$, DefExt$)
OPENFILENAME.hInstance = 0
OPENFILENAME.lpstrCustomFilter = 0
OPENFILENAME.nMaxCustFilter = 0
OPENFILENAME.lpstrInitialDir = lstrcpy(szCurDir$, szCurDir$)
OPENFILENAME.nFileOffset = 0
OPENFILENAME.nFileExtension = 0
OPENFILENAME.lCustData = 0
OPENFILENAME.lpfnHook = 0
OPENFILENAME.lpTemplateName = 0
'* This will pass the desired data structure to the Windows API,
'* which in turn uses it to display the Open Dialog form.
APIResults% = GetOpenFileName(OPENFILENAME)
If APIResults% <> 0 Then
'* Note that FileName$ will have an embedded Chr$(0) at the
'* end. You may wish to strip this character from the string.
FileName$ = Left$(FileName$, InStr(FileName$, Chr$(0)) -1)
Message$ = "The file you chose was " + FileName$
Else
Message$ = "No file was selected"
End If
MsgBox Message$
End Function
Hope this helps John. I copied it from the Microsoft Access 2.0
KnowledgeBase Q96114, just so you know. You should download the
KnowledgeBase yourself.
John Travis Beavers <jbea...@cs.utexas.edu> wrote in article
<Pine.LNX.3.96.971218...@chef.cs.utexas.edu>...
Dev Ashish
Look in Solutions.MDB sample database for this function (same name).
Unfortunately, unlike Excel, there's no single pre-defined function that
does that in Access.
HTH
--
Dev Ashish
John Travis Beavers wrote in message ...
LMLinson
In article <Pine.LNX.3.96.971218...@chef.cs.utexas.edu>, John
Travis Beavers <jbea...@cs.utexas.edu> writes:
>Does anybody know how to open a file dialog box from a script in VBA
>under Access? In Excel I would have used getOpenFilename but there
>doesn't appear to be any such function for Access as far as I can tell.
>(Why isn't it consistent?)
Look in the Solutions sample database that comes with Access.
It'll be in your Office folders hierarchy in a Samples folder. The
example of using the Windows Common Dialog GetOpenFileName
and GetSaveFileName functions is discussed under the topic
"Using multiple databases", subtopic "Relinking tables". The code
is in module basReLinkTables, I believe.
They chose to encapsulate use of this API in an Excel function.
With Visual Basic and the Office 97 Developer Edition, they
provided an ActiveX control. Why? You'd have to ask some
Senior Designer at Microsoft, not us users here in the newsgroup.
It's awfully easy to use the API directly, so I couldn't say why
they'd create an ActiveX; a builtin function might be even easier.
Kurt A. Fisher
> >Does anybody know how to open a file dialog box from a script in VBA
> >under Access?
See prior post, reproduced here:
On 10 Nov 1997 14:44:28 GMT in comp.databases.ms-access, "Brian Ward"
> <bw...@rrcc.mb.ca> noted: Use the [... solutions.mdb....] It has all the
code
> > commented on how to use the Open File Dialog box API call.
The code in the Solutions.mdb under module modRefreshLinks, demonstrates
the use of a direct Win32API call to use the Windows Open dialogue box.
Study of Solutions.mdb and the use of the direct call to GetOpenFileName
function in Win32API is one and usually the better option.
Another method of interfacing with the open dialogue box is through the
ActiveX common dialogue control. I favor the ActiveX control because it is
easier to program, particularly if you also want to use the "common
control" library to also access the change printer dialogue.
The following are some instructions for a form based on a command button
and on Access 97 Help. See Access 97 Help on topic "CommonDialog Control
(Open, Save As Dialogs)" The following example code prompts for a "text"
or "rft" type filename. By changing the Filters property, you can alter
the type of file the common dialogue prompts for. The code gets the
filename and displays it in a message box.
1) First you create a new form.
2) If you have the ActiveX Microsoft Common Dialogue Control in your
ActiveX Tools list, drag it to your form. It sits in the background and
is invisible. Access will assign a garbage name to the control like
"Actxctrl1"
3) Open the properties sheet for the ActiveX common dialogue control.
Change the name of the control (not the caption) to "CommonDialog1". You
have to change the name for the control for it to be correctly referenced
in the following code.
4) Create blank command button. Name your button "cmdOpen" in order for it
to be correctly referenced by the following code.
5) On the properties sheet, event tab, create a new code procedure on the
"Click" event. This will put you in a code-behind-forms module window at
the blank subroutine "cmdOpen_Click".
6) Paste the following code in the module:
'----------------------------------------------------------------------
Private Sub cmdOpen_Click()
'Get a file name and print it in a message box
'. Acknowledgements: Microsoft Access 97 Help example.
'. . This routine was originally used to open a rtf or text file to edit
'. Set CancelError is True
On Error GoTo ErrHandler
'. Use the common dialogue control
CommonDialog1.CancelError = True
'. Set flags to control the dialogue window's appearance and response
'. . Note: Adding control constants applies multiple flags
Dim lngDialogue_settings
'. . Hide read only files
lngDialogue_settings = lngDialogue_settings + cdlOFNHideReadOnly
CommonDialog1.Flags = lngDialogue_settings
'. Set filters
CommonDialog1.Filter = "Text Files (*.txt)|*.txt|" & _
"Rich Text (*.rtf)|*.rtf|Batch Files (*.bat)|*.bat|All Files (*.*)|*.*|"
'. Specify default filter, e.g. the 1st, 2nd, 3rd or 4th item in the list
'. in the preceeding statement, i.e. to: Text Files (*.txt)
CommonDialog1.FilterIndex = 1
'. Initialize/clear the filename and the box
CommonDialog1.FileName = ""
'. Display the Open dialog box
CommonDialog1.ShowOpen
'Do something with the name that was returned.
MsgBox "You selected this file" & CommonDialog1.FileName
Exit Sub
ErrHandler:
'. User pressed the Cancel button. So do nothing but exit
'. . but here's a complimentary error trapper, if you need it.
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") has occurred."
Exit Sub
End Sub
(The foregoing code was adapted from a Rich Text Box control example I have
been working on. I have not debugged the foregoing example after editing
the source code for this post. You may get a complier error.)
There is also another example mdb, the ActiveX controls sample db, that
also contains a sample form. The ActiveX control sample mdb is available
through:
http://www.microsoft.com/AccessDev/a-a&sa.htm
(All of the Active X sample database may not work because the database may
require several ActiveX controls that are distributed only with the Office
Developer's Edition. The Common dialogue control example should work. The
control is distributed as part of Windows 95 and Access 97. But this is a
"hot" sample database and still worth your time to download it.)
This Active X control is also discussed in:
Access 97 Help on topic "CommonDialog Control (Open, Save As Dialogs)"
Campbell, Richard. October 1997. A Little Common Dialog, Please . . .
Access Office VB Advisor. http://www.advisor.com p.48-57.