Hyperlinking to a folder, not a file

[angi35]

Does anyone know if it's possible to create a command button that opens a folder (in Windows Explorer or My Computer) rather than a specific file document? If so, how? I have a team of users that just wants a hyperlink from an Access record to a folder on the server containing a group of files related to the record, rather than creating an individual link to each file.

I'm working in Access 2000.

Angi

[nico5038]

Not sure or a hyperlink will accept a folder, but personally I store the Path in a field in a record and use a button that activates a folder popup menu from windows to allow the user to select the folder from a treeview like (s)he's used from the explorer.

' Form code for the OnClick event of a button named "GetFolder"
 
Private Sub btnGetFolder_Click()
Dim strPath As String
 
strPath = fncGetFolder(vbCrLf & "Export to folder:", Me.Hwnd)
 
If Len(strPath) > 0 Then
   Me.ExportFolder = strPath
   ' force a save to get the path certainly available...
   DoCmd.RunCommand acCmdSaveRecord
End If
 
End Sub
 
' Module code to handle the fncGetFolder() function by using ole32.dll 
 
Option Compare Database
Option Explicit
 
Type shellBrowseInfo
    hWndOwner      As Long
    pIDLRoot       As Long
    pszDisplayName As Long
    lpszTitle      As String
    ulFlags        As Long
    lpfnCallback   As Long
    lParam         As Long
    iImage         As Long
End Type
 
Const BIF_RETURNONLYFSDIRS = 1
Const MAX_PATH = 260
 
Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Declare Function SHBrowseForFolder Lib "shell32" (lpbi As shellBrowseInfo) As Long
Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, _
         ByVal lpBuffer As String) As Long
 
'Then use the following function, supplying it the title you want to use for the dialog, and the handle of the calling form. (use the Me.hwnd property of the form):
Public Function fncGetFolder(dlgTitle As String, Frmhwnd As Long) As String
 
    Dim intNullChr As Integer
    Dim lngIDList As Long
    Dim lngResult As Long
    Dim strFolder As String
    Dim BI As shellBrowseInfo
 
    With BI
        .hWndOwner = Frmhwnd
        .lpszTitle = dlgTitle
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
 
    lngIDList = SHBrowseForFolder(BI)
    If lngIDList Then
        strFolder = String$(MAX_PATH, 0)
        lngResult = SHGetPathFromIDList(lngIDList, strFolder)
        Call CoTaskMemFree(lngIDList)        'this frees the ole pointer to lngIDlist
        intNullChr = InStr(strFolder, vbNullChar)
        If intNullChr Then
            strFolder = Left$(strFolder, intNullChr - 1)
        End If
    End If
 
    fncGetFolder = strFolder
 
End Function
 

Hope this is also usefull.

Nic;o)

[angi35]

Not sure or a hyperlink will accept a folder, but personally I store the Path in a field in a record and use a button that activates a folder popup menu from windows to allow the user to select the folder from a treeview like (s)he's used from the explorer.

Well, the code is way beyond me, but I put it all in, and it worked. Amazing.

At least it partly worked. As I understand it, I should be able to go into the record, click the btnGetFolder and browse for a folder, then click "OK", and that path should be stored in a field in my table called ExportFolder that's also a control on my form. Then, I'm assuming, next time I click the button in this record, it should open the browser directly to that folder (?). Right now I open the browse box, follow the path to my selected folder, click "OK", and the browse box just goes away. Then when I click the button again, the box comes up and I start back at the beginning. How do I get it to store my selected path?

Also, when this is fully working, will it just save the path so that a user can see where the folder is but then has to go outside Access to Explorer to get to the folder and the files they're looking for -- or will it be possible for the user to open the folder and open documents within the folder from right there?

Thanks,
Angi

[nico5038]

I just use this to allow the user to navigate to a folder.
In my application I then execute some code to fill a table with the files of the folder and show that in a subform.
That (temp) table could be used with a YesNo field to allow for file(s) selection.

The code for the get files function is:

Function fncImportFilenames(strPath As String)
 
Dim rs As DAO.Recordset
'This function will import filesnames from the passed path into table "tblFiles"
'Make sure the strPath has a trailing "\"
If Right(strPath, 1) <> "\" Then
   strPath = strPath & "\"
End If
    Set rs = CurrentDb.OpenRecordset("tblFiles")
    Dim fs, f, f1, fc, s, tso
    Set fs = CreateObject("Scripting.FileSystemObject")
    ' file directory
    Set f = fs.GetFolder(strPath)
    ' file collection
    Set fc = f.Files
    For Each f1 In fc
        rs.AddNew
        rs!FileName = f1.Name
        rs!YesNo = False
        rs.Update
    Next
 
End Function
 

Define a table named "tblFiles" with a 250 character FileName text field and a YesNo field named "YesNo".
Getting the idea ?

Nic;o)

[ADezii]

Does anyone know if it's possible to create a command button that opens a folder (in Windows Explorer or My Computer) rather than a specific file document? If so, how? I have a team of users that just wants a hyperlink from an Access record to a folder on the server containing a group of files related to the record, rather than creating an individual link to each file.

I'm working in Access 2000.

Angi

You can accomplish what you are requesting with as little as 4 lines of code, if you only intention is to View the Files within a specific Folder and not interact with them. For something this simple, I would use the Office File Dialog to allow the User to select only a single Folder, pass the Folder Name to the code below as strFolderName, then open it. If you are interested in this approach, let me know and I'll provide the remainder of the code.

Dim retVal As Variant
Dim strFolderName As String
 
strFolderName = "C:\Test"
retVal = Shell("Explorer.exe " & strFolderName, vbMaximizedFocus)

[ADezii]

Hello NIco, nice touch on your Folder Browsing code, very practical and familiar to the User.

[nico5038]

Hello NIco, nice touch on your Folder Browsing code, very practical and familiar to the User.

Thanks ADezii, came up with this when I ran into the .dll hell of the Open/Save dialog. This dialog worked on all PC's for me without "Reference errors" :-)

Nic;o)

[ADezii]

Thanks ADezii, came up with this when I ran into the .dll hell of the Open/Save dialog. This dialog worked on all PC's for me without "Reference errors" :-)

Nic;o)

Hello again , Nico. Are you aware of any other values for the ulFlags Element besides BIF_RETURNONLYFSDIRS within the ShellBrowseInfo Type? Just curiosity.

With BI
  .hWndOwner = Frmhwnd
  .lpszTitle = dlgTitle
  'any other Flag values you are aware of?
  .ulFlags = BIF_RETURNONLYFSDIRS
End With

[nico5038]

Hello again , Nico. Are you aware of any other values for the ulFlags Element besides BIF_RETURNONLYFSDIRS within the ShellBrowseInfo Type? Just curiosity.

With BI
  .hWndOwner = Frmhwnd
  .lpszTitle = dlgTitle
  'any other Flag values you are aware of?
  .ulFlags = BIF_RETURNONLYFSDIRS
End With

Found: http://www.earlsoft.co.uk/projects/c...lderDialog.cls
Holding:

'Browse flags
Private Const BIF_RETURNONLYFSDIRS = &H1 'For finding a folder to start document searching
'Private Const BIF_DONTGOBELOWDOMAIN = &H2 'For starting the Find Computer
'Private Const BIF_STATUSTEXT = &H4
'Private Const BIF_RETURNFSANCESTORS = &H8
Private Const BIF_EDITBOX = &H10
'Private Const BIF_VALIDATE = &H20 'insist on valid result (or CANCEL)
Private Const BIF_NEWDIALOGSTYLE = &H40 'New style dialog box
Private Const BIF_NONEWFOLDERBUTTON = &H200 'Hide the new folder dialog box
'Private Const BIF_BROWSEFORCOMPUTER = &H1000 'Browsing for Computers.
'Private Const BIF_BROWSEFORPRINTER = &H2000 'Browsing for Printers
Private Const BIF_BROWSEINCLUDEFILES = &H4000 'Browsing for Everything
 

But never used another value as the one specified.

Nic;o)

[ADezii]

Found: http://www.earlsoft.co.uk/projects/c...lderDialog.cls
Holding:

'Browse flags
Private Const BIF_RETURNONLYFSDIRS = &H1 'For finding a folder to start document searching
'Private Const BIF_DONTGOBELOWDOMAIN = &H2 'For starting the Find Computer
'Private Const BIF_STATUSTEXT = &H4
'Private Const BIF_RETURNFSANCESTORS = &H8
Private Const BIF_EDITBOX = &H10
'Private Const BIF_VALIDATE = &H20 'insist on valid result (or CANCEL)
Private Const BIF_NEWDIALOGSTYLE = &H40 'New style dialog box
Private Const BIF_NONEWFOLDERBUTTON = &H200 'Hide the new folder dialog box
'Private Const BIF_BROWSEFORCOMPUTER = &H1000 'Browsing for Computers.
'Private Const BIF_BROWSEFORPRINTER = &H2000 'Browsing for Printers
Private Const BIF_BROWSEINCLUDEFILES = &H4000 'Browsing for Everything
 

But never used another value as the one specified.

Nic;o)

Thanks alot for the info, Nico.

[angi35]

You can accomplish what you are requesting with as little as 4 lines of code, if you only intention is to View the Files within a specific Folder and not interact with them. For something this simple, I would use the Office File Dialog to allow the User to select only a single Folder, pass the Folder Name to the code below as strFolderName, then open it. If you are interested in this approach, let me know and I'll provide the remainder of the code.

Dim retVal As Variant
Dim strFolderName As String
 
strFolderName = "C:\Test"
retVal = Shell("Explorer.exe " & strFolderName, vbMaximizedFocus)

Hi ADezii -- It depends on what you mean by 'interact with them'. If you mean sharing data between the database and the external file, no, there's no need for that.

The situation I have is that there's a section of my database that captures key engineering info on each job record. The engineering department has seen how in other areas of the database, other departments are able to click a button and open an external document that's been hyperlinked. They'd like this function too, but they have so many files - engineering drawings and so on - for each job, that what they'd really like is to be able to click a button that would lead them to the folder where all these files are kept, and show all the files. From there they would double-click on the file of their choice and be able to open and work in it.

So what I'm imagining is creating a button... or a textbox that I'll make look and act like a button... that saves a path to the folder containing the engineering files related to the specific job record. The engineer user can click that button, which opens Explorer to that path and shows the folder with all its files & subfolders, and then he/she can open whatever file.

I guess the idea is to open a specific location in Explorer in the same way you can open a specific document in Word or Acrobat.

If something like that is possible with the code you have in mind, then I'd certainly like to have the rest of it!

Thanks.
Angi

[angi35]

I just use this to allow the user to navigate to a folder.
In my application I then execute some code to fill a table with the files of the folder and show that in a subform.
That (temp) table could be used with a YesNo field to allow for file(s) selection.

The code for the get files function is:

Function fncImportFilenames(strPath As String)
 
Dim rs As DAO.Recordset
'This function will import filesnames from the passed path into table "tblFiles"
'Make sure the strPath has a trailing "\"
If Right(strPath, 1) <> "\" Then
   strPath = strPath & "\"
End If
    Set rs = CurrentDb.OpenRecordset("tblFiles")
    Dim fs, f, f1, fc, s, tso
    Set fs = CreateObject("Scripting.FileSystemObject")
    ' file directory
    Set f = fs.GetFolder(strPath)
    ' file collection
    Set fc = f.Files
    For Each f1 In fc
        rs.AddNew
        rs!FileName = f1.Name
        rs!YesNo = False
        rs.Update
    Next
 
End Function
 

Define a table named "tblFiles" with a 250 character FileName text field and a YesNo field named "YesNo".
Getting the idea ?

Nic;o)

Hi Nico,
I'm trying, but I'm afraid I'm not really getting the idea. First off, where do I put this function? I mean what event do I attach it to? How do I make it run?

Also, I don't think the "ExportFolder" field from the code you sent in your first post is doing anything. That's supposed to be the place that saves the path to the folder I select, right? -- so that the next time I click the btnGetFolder, it will show directly the folder I selected previously? Nothing is getting saved in that field. I can even delete the lines:

If Len(strPath) > 0 Then
    Me.ExportFolder = strPath
 
    DoCmd.RunCommand acCmdSaveRecord
End If

... it makes no difference to how the button operates.

I'm also wondering about the new function you gave me... Is it going to be possible to open each file from the subform? If a new file is added to the folder, or another file is deleted, is there going to be a way to update the subform that has all the file names? (I'm also assuming it's not going to matter if the files aren't the usual Office or pdf files - mostly they're in some sort of specialized engineering drawing software.)

In reality, I don't really need a subform with the specific files, unless that's the only way this can work (I already have so many subforms and nested subforms!). I don't need the files to be linked to the database. If it would be possible simply (simply?) to view the folder and all the files in it, then double-click on a file to open it without saving the file path in the database, that would be ideal. I explained the situation in more detail in the post I just put up in response to ADezii's suggestion.

Thanks for your help.
Angi

[nico5038]

For activating explorer with the registered path you can use te Shell() function like:

Shell ("explorer.exe " & Chr(34) & Me.txtPath & Chr(34))
 

Nic;o)