Needing browse capability WITHOUT access to the Common Dialog Control 9

[Katerine]

Help please.. :-I

I need to find a way to program a browse function, but I don't have access to the Common Dialog Control here at work. In several places on the net, I've located source code for browsing for folders, but I haven't been able to find anything to browse for files. I've looked for possibly relevant shell32.dll functions, but I've only found the "SHBrowseForFolder" and the "SHGetPathFromIDList" functions on the net, and these appear to only deal with folders, not files.

I'm starting to pull my hair out.. there has to be a way! There's always a way!

TIA for any help you can provide!

 

[RickSpr]

Katerine,

You may not have the Common Dialog Control VB object, but you must surely have comdlg32.dll on the system, unless you're using a very old version of Windows and haven't upgraded Internet Explorer. If you have comdlg32.dll, you can use the following class module.

Note that if you're using Access 97 or earlier, the Enum keyword used to define values for the Flags argument isn't valid. After the class module I've included a standard module that contains Const definitions to use instead.

--------------- Class module ---------------

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                     CommonDialog class                       '
'                                                              '
'      This module contains an interface to the Common         '
'      Dialog File Open/Save functions. It may be enhanced     '
'      for other Common Dialog functions at a future date.     '
'                                                              '
'      This object presents exactly the same interface as      '
'      the Microsoft Common Dialog 6.0 library from Visual     '
'      Basic 6.0 (comdlg32.dll).                               '
'                                                              '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Compare Database
Option Explicit

' Values for the Flags property; multiple values can be ORed together.
' In versions of Access prior to Access 2000, comment or delete these
' and use the CommonDialogConstants module (Enum keyword is not
' valid in these versions).
Public Enum CmdlgOpenFlags
     cdlOFNAllowMultiselect = &H200
     cdlOFNCreatePrompt = &H2000
     cdlOFNExplorer = &H80000
     cdlOFNFileMustExist = &H1000
     cdlOFNHideReadOnly = &H4
     cdlOFNNoChangeDir = &H8
     cdlOFNNoDereferenceLinks = &H100000
     cdlOFNNoNetworkButton = &H20000     ' not documented for common dlg
     cdlOFNNoReadOnlyReturn = &H8000
     cdlOFNNoValidate = &H100
     cdlOFNOverwritePrompt = &H2
     cdlOFNPathMustExist = &H800
     cdlOFNReadOnly = &H1
     cdlOFNShowHelp = &H10
     cdlOFNShareAware = &H4000
     cdlOFNExtensionDifferent = &H400
End Enum

' Errors raised
Public Enum CmdlgErrors
    cdlCancel = 32755                   ' user pressed Cancel in dialog
End Enum

' Filter string used for the Open/Save dialog filters (the
' "Files of type" combo box). The string consists of a list of
' filter specs, each of which consists of a pair of elements.
' The first element of each spec is the description to appear
' in the combo box, and the second is a filter pattern. When
' the user selects the description, the corresponding pattern
' is used to filter the list of files in the file list box.
' A pipe character ("|") separates each element of the string.
' Example: "Database Files|*.mdb|All Files|*.*"
Public Filter As String
' Initial Filter to display. This sets/returns the index of the
' currently selected item in the filter combo box.
Public FilterIndex As Long
' Initial directory for the dialog to open in.
' Default = Current working directory.
Public InitDir As String
' Initial file name to populate the dialog with. Default = "".
' Returns the full path name of the selected file.
Public FileName As String
' Returns file name (without path) of file picked
Public FileTitle As String
' Title to appear on the dialog box.
Public DialogTitle As String
' Default extension to append to file if user didn't specify one.
Public DefaultExt As String
' Flags (see constant list) to be used.
' Returns cdlOFNDifferentExtension if extension present and not = DefaultExt
Public Flags As Long
' Maximum length of the file name to be returned
Public MaxFileSize As Integer
' Set to True to raise error 32755 if user cancels dialog box
Public CancelError As Boolean

' Constants used when raising errors
Private Const ErrSource = "MyComDlg.CommonDialog"

' Interface to Win32
Private Type W32_OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustrFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    lngFlags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustrData As Long
    lpfnHook As Long
    lpTemplateName As Long 'String
End Type

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
    "GetOpenFileNameA" (pOpenfilename As W32_OPENFILENAME) As Boolean
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
    "GetSaveFileNameA" (pOpenfilename As W32_OPENFILENAME) As Boolean

Private Sub Class_Initialize()
    ' Initialize the MaxFileSize to minimum, in case the user doesn't set it
    MaxFileSize = 256
End Sub

Public Sub ShowOpen()
' Shows the Open dialog
    
    Dim wofn As W32_OPENFILENAME
    Dim intRet As Integer

    OFN_to_WOFN wofn
    On Error GoTo ShowOpen_Error
    intRet = GetOpenFileName(wofn)
    On Error GoTo 0
    WOFN_to_OFN wofn
    If (intRet = 0) And CancelError Then _
        Err.Raise cdlCancel, ErrSource, "File open canceled by user"
    Exit Sub

ShowOpen_Error:
    WOFN_to_OFN wofn
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, _
        Err.HelpContext
End Sub

Public Sub ShowSave()
' Shows the Save dialog
    
    Dim wofn As W32_OPENFILENAME
    Dim intRet As Integer

    OFN_to_WOFN wofn
    On Error GoTo ShowSave_Error
    intRet = GetSaveFileName(wofn)
    On Error GoTo 0
    WOFN_to_OFN wofn
    If (intRet = 0) And CancelError Then _
        Err.Raise cdlCancel, ErrSource, "File save canceled by user"
    Exit Sub

ShowSave_Error:
    WOFN_to_OFN wofn
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, _
        Err.HelpContext
End Sub

Private Sub OFN_to_WOFN(wofn As W32_OPENFILENAME)
' This sub converts from the Microsoft Access structure to the Win32 structure.
    
    ' Initialize some parts of the structure.
    With wofn
        .hwndOwner = Application.hWndAccessApp
        .hInstance = 0
        .lpstrCustomFilter = vbNullString
        .nMaxCustrFilter = 0
        .lpfnHook = 0
        .lpTemplateName = 0
        .lCustrData = 0
        .lpstrFilter = ConvertFilterString(Filter)
        .nFilterIndex = FilterIndex
        If MaxFileSize < 256 Then MaxFileSize = 256
        If MaxFileSize < Len(FileName) Then MaxFileSize = Len(FileName)
        .nMaxFile = MaxFileSize
        .lpstrFile = FileName & String(MaxFileSize - Len(FileName), 0)
        .nMaxFileTitle = 260
        .lpstrFileTitle = String(260, 0)
        .lpstrTitle = DialogTitle
        .lpstrInitialDir = InitDir
        .lpstrDefExt = DefaultExt
        .lngFlags = Flags
        .lStructSize = Len(wofn)
    End With
End Sub

Private Sub WOFN_to_OFN(wofn As W32_OPENFILENAME)
' This sub converts from the Win32 structure to the Microsoft Access structure.
    
    With wofn
        FileName = Left$(.lpstrFile, InStr(.lpstrFile, vbNullChar) - 1)
        FileTitle = Left$(.lpstrFileTitle, InStr(.lpstrFileTitle, vbNullChar) - 1)
        FilterIndex = .nFilterIndex
        Flags = .lngFlags
    End With
End Sub

Private Function ConvertFilterString(strFilterIn As String) As String
' Creates a Win32 filter string from a pipe (&quot;|&quot;) separated string.
' The string should consist of pairs of filter|extension strings,
' i.e. &quot;Access Databases|*.mdb|All Files|*.*&quot;
' If no extensions exists for the last filter pair, *.* is added.
' This code will ignore any empty strings, i.e. &quot;||&quot; pairs.
' Returns &quot;&quot; if the string passed in is empty.
    
    Dim strFilter As String
    Dim intNum As Integer, intPos As Integer, intLastPos As Integer

    strFilter = &quot;&quot;
    intNum = 0
    intPos = 1
    intLastPos = 1

    ' Add strings as long as we find pipe characters
    ' Ignore any empty strings (not allowed).
    Do
        intPos = InStr(intLastPos, strFilterIn, &quot;|&quot;)
        If (intPos > intLastPos) Then
            strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos) & vbNullChar
            intNum = intNum + 1
            intLastPos = intPos + 1
        ElseIf (intPos = intLastPos) Then
            intLastPos = intPos + 1
        End If
    Loop Until (intPos = 0)
        
    ' Get last string if it exists (assuming strFilterIn was not bar terminated).
    intPos = Len(strFilterIn)
    If (intPos >= intLastPos) Then
        strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos + 1) & vbNullChar
        intNum = intNum + 1
    End If
    
    ' Add *.* if there's no extension for the last string.
    If intNum Mod 2 = 1 Then
        strFilter = strFilter & &quot;*.*&quot; & vbNullChar
    End If
    
    ' Add terminating NULL
    ConvertFilterString = strFilter & vbNullChar
End Function

----------------- Standard module -------------

Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                CommonDialog class constants                  '
'                                                              '
'      This module is used with the CommonDialog class in      '
'      versions of Access prior to Access 2000. These          '
'      versions don't allow Enums to be defined.               '
'                                                              '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Values for the Flags property; multiple values can be ORed together
Public Const cdlOFNAllowMultiselect = &H200
Public Const cdlOFNCreatePrompt = &H2000
Public Const cdlOFNExplorer = &H80000
Public Const cdlOFNFileMustExist = &H1000
Public Const cdlOFNHideReadOnly = &H4
Public Const cdlOFNNoChangeDir = &H8
Public Const cdlOFNNoDereferenceLinks = &H100000
'Public Const cdlOFNNoNetworkButton = &H20000     ' not documented for common dlg
Public Const cdlOFNNoReadOnlyReturn = &H8000
Public Const cdlOFNNoValidate = &H100
Public Const cdlOFNOverwritePrompt = &H2
Public Const cdlOFNPathMustExist = &H800
Public Const cdlOFNReadOnly = &H1
Public Const cdlOFNShowHelp = &H10
Public Const cdlOFNShareAware = &H4000
Public Const cdlOFNExtensionDifferent = &H400

' Error raised if user presses the Cancel button
Public Const cdlCancel = 32755
Public Const cdlFailure = 32756

Rick Sprague

 

[Katerine]

Thanks so much! I'll give that a try tomorrow at work.

You didn't write all that just for me, did you??

 

[RickSpr]

No, it's part of my library. I use it a lot. I'm sorry I didn't have the flags documented. If you have MSDN, you can look them up in the OPENFILENAME struct. Otherwise let me know and I'll whip up a summary. Rick Sprague

 

[RickSpr]

Katerine,

I had to use this code myself yesterday and discovered a bug. If you use certain of the Flags values, then ShowOpen and ShowSave will refuse to work. The problem is that these flags values should be Longs, but by default they're made Integers by VBA.

The fix is to change each of the constants for the flag values to append "&" to the end of the value. This forces the constant to a Long.

I hope I didn't waste a lot of your time with this. Rick Sprague

 

[missyu]

Katerine,

Did this work for you? I'm using Access 97 and I'm having problems trying to use it. Do you just put the code that Rick provided above behind a button? (I'm trying to use it so that On_Click, I can browse for a file - is this the intent?) All I did was paste the code On_Click of a command button and then I put the CommonDialogue class constants in a module. What am I doing wrong?

*******************************************************
in code, line:
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As W32_OPENFILENAME) As Boolean
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
"GetSaveFileNameA" (pOpenfilename As W32_OPENFILENAME) As Boolean

I get this error:
Only comments may appear after End Sub, End Function or End Property

*********************************************************
Line:
Private Sub OFN_to_WOFN(wofn As W32_OPENFILENAME

Error:
User defined type not defined

Any insight in this would be great.
Thanks
Miss

 

[Katerine]

Missyu,
Yes, it did work just fine for me (Thanks, Rick! ).

Here is what I did (and this may help explain why it was not working in your case):

I copied and pasted the code into a class module. (to do this in Access 97: with the database window active, select Insert | Class Module. I named this class module, "clsCommonDialog". The name is actually pertinant for class modules -- this becomes the name of the object type.

I commented out all the Enums because it's Access 97, and created another module (a regular one this time), and pasted the constants into it (and called this "modCommonDialog&quot.

Then, in a form, I created a button with the following code:

--- Begin code ---
Dim strFileFolderName As String
Dim cmdlgOpenFile As New clsCommonDialog
Const clngFilterIndexAll = 5

cmdlgOpenFile.Filter = "Database Files (MDB)|*.mdb|" & _
"Text Files (TXT)|*.txt|PDF Files (PDF)|*.pdf|" & _
"HTML Files (HTM,HTML)|*.htm*|All Files (*.*)|*.*"

' Set the default file type selection to "All"
' (the fifth item in the above list)
cmdlgOpenFile.FilterIndex = clngFilterIndexAll

cmdlgOpenFile.ShowOpen

strFileFolderName = cmdlgOpenFile.FileName
--- End Code ---

There's probably some better ways and some niftier things that can be done with this, but this works for my purposes.

My guess is, the first error occured because the code Rick provided is only meant to work in class modules. Form modules need to have everything in Sub or Function procedures. And type declarations only work in global modules, not in form modules, so it didn't recognize "W32_OPENFILENAME" as a valid data type.

Hope that helps! Rick, please feel free to elaborate/correct me if I missed anything or got anything wrong.

 

[missyu]

Katerine (& Rick),

You are AWESOME!! This worked beautifully. Rick's instructions were great but it was your step by step instructions that helped me set this up properly!

Thanks a million!

 

[OC2]

Hi I have used this code to create a file browser for my access application.

Thanks for the advice guys.
One problem though,

When I enter the browse window, how do i get for example,

the file name to show me!savename to appear on the filename box like when you save a file in Excel. If the workbook is called "book1" when you press save as, "book1" has appeared in the file name text box.

Any ideas?
thx

 

[Katerine]

Hi OC2,
This should do it:

Add the following in the code I posted above, somewhere between, "Dim cmdlgOpenFile As New clsCommonDialog" and "cmdlgOpenFile.ShowOpen":

cmdlgOpenFile.FileName = "book1"

I didn't test that.. hope it helps..

 

[Guest_imported]

Katerine and Rick --

Just wanted to say it's better than an year after this original post, and I still found it easy to use and extremely helpful... I feel like I have been saved or something....

THANKS FOR SHARING!

 

[chancer101]

This is FANTASTIC

Many thanks,
just what I was after

Paul

 

[AndyTilia]

Woo-hoo! Today is exactly 15 months after the original post. I just set this up and it works great. Thank you guys-- you've provided a valuable service.

 

[daviesjustin]

This is awesome. Thanks guys.
One probably stupid question:
The browse button works, i select a file of my choice, i select and d-click on it and then am returned to my Access form. Where has the file i selected gone?

Hope this isn't too rediculous a question!

 

[wellsbk]

This is a great thread. I used it to sucessfully to add the common dialog to my Access database.

Now I would like to add a common dialog to an Excel spreadsheet. Does anyone know how to approach this issue. The code is not directly compatible because in clCommonDialog there are access specifc properties that are referenced, for example:

With wofn
        .hWndOwner = Application.hWndAccessApp
[\code]
There is no obvious subsitution in excell's Application. object.  And I looked through Randy Birch's website that gives an over view of the class, but I'm still not sure how to proceed.
[URL unfurl="true"]http://www.mvps.org/vbnet/[/URL]    

Search for "openfilename"

Thanks, 
Brian Wells  Space Research Institute, Auburn University

 

[wellsbk]

I found the answer to my problem. It much simpler than working with Access. Excel has a Application.GetOpenFilename not found in Access. This is documented by Romke Soldaat at

http://msdn.microsoft.com/library/d...ry/en-us/dnovba00/html/CommonDialogsPartI.asp

He also outlines how to approach the problem using the API class outlined in the previous postings. I toyed with it and fould that by removing the "Compare database" and seting wofn.hWndOwner=0 it works.

The handle is orginally set to Application.hWndAccessApp but

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wceui40/htm/cerefOPENFILENAME.asp

states that the property can be set to NULL if the window has no owner.

I don't know what purpose it has in the orginal. Also I attempted to find the handle to the excel window, as I don't like to arbitrarily set properties to zero that are giving me probems as this tend to bite me later. But I couldn't find what I was looking for. And I got the functionallity I wanted from the GetOpenFilename.
Thanks for the help
Brian Wells

 

[mattdrinks]

An excellent tread, Rick your code is great I have it working in Access 2000. Just one problem I can not get the newer stlye explorer file open dialog to show, I have the following code in a button on a form but it will only show the older style dialog box.

Private Sub cmdGetData_Click()

Dim cmdOpenFile As New clsOpenSaveDialog

cmdOpenFile.ShowOpen
strFileName = cmdOpenFile.FileName

Debug.Print strFileName

End Sub

I have tried adding the following line

cmdOpenFile.Flags = cdlOFNExplorer

but it makes no difference. I have looked on the MSDN for an answer but can not find out why this does not work. I can get the explorer version of the dialog to show using the Application.GetOpenFilename Method of Excel, but this means opening an instance of Excel from Access and this is much slower then the API call.

I know this is an old thread but does anyone have any ideas?

 

[thedeadmilkman]

The Cdode is working great Rick, But I'm having a problem that I just can't figure out. I'm putting the location of the file into a hyperlink field, but when i click on the link it won't open the file. When I right click and edit the Hyperlink and type in the file name it works fine, is there anyway I can do this, without having to do it manually??

thanks

 

[TopJack]

Wooow

http://www !

Nearly three years after the first post and still these posts are brilliant. I was trying to use the open file dialog box in Access 2000 and found the same problems. The link from wellsbk to msdn was excellent.

Cheers guys.

 

[sard0nicpan]

Unbelievable! I merely had to cut and paste the code contained herein and I was up and running! A definite keeper!