Linking document to record VBA

[samg2013]

Hello,

I have a form displaying records. I would like the user to be able to select a button, browse for a file on a shared drive and link it to that record.

I have been exploring hyperlinking, which works for the most part, although I need it to display the share name rather than the drive assignment for that user. Such as \\drive1\folder rather than C:\folder.

Hyperlinking also unfortunatly requires the user to right click on a field select edit hyperlink then browse. Not a very good end solution.

Can this be done in VBA?

THanks for your help as always.

 

[MarkK]

It's not exactly clear to me what you mean. Do you need to select a file on a disk? If so, look into the Office.FileDialog object. Otherwise you need to be more specific about what a "Link" is to you, like, do you want to insert a file path into a field in a table? And then, I don't see how a hyperlink satisfies either of those requirements . . .

Confused

 

[samg2013]

Thanks for the reply Mark, I'll try and explain a bit.

The idea is each record will need to be 'linked' in someway to a pdf or doc file that lives on a shared drive.

I currently have a field for the user to add a hyperlink to the file, which works but does not give the best user experience.

Perfect world.

THe user navigates to the record required via a form. Selects 'browse' locates a file on a shared drive, clicks 'ok' and then that file is linked to that record in some way.

A view button would then allow the user to open that file.

Any suggestions?

 

[MarkK]

1)

THe user navigates to the record required via a form. Selects 'browse' locates a file on a shared drive, clicks 'ok'

to do this part, use the FileDialog object. Sample code, if you have set a reference to Office, might be . . .

Private Function GetFilename() As String
    With FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False [COLOR="Green"]'only allow selection of a single file[/COLOR]
        .InitialFileName = "C:\PathFoFiles\WhenItOpens\"
        .ButtonName = "The Text On the OK Button"
        .Title = "The title in the selection window"
        .Show [COLOR="Green"]'shows a modal window to select a file[/COLOR]
        [COLOR="Green"]'code pauses until selection window is cleared[/COLOR]
        If .SelectedItems.count > 0 Then
            MsgBox "This is the selected filename: " & .SelectedItems(1)
            GetFilename = .SelectedItems(1)
        End If
    End With
End Function

2) Then you can save that filename to disk in the record you are talking about.

3) Open the file programmatically: check out this web page, http://support.microsoft.com/kb/170918, which shows how you can run the windows program associated with a file, and open said file, in VBA code.

Post back if you get stuck,

 

[GaryPanic]

Odd ?
Is this a master document or a individual file (say a letter from x)

what I have (If this helps is a folder per record and I drop all files in to the folder -
user can just click open folder from the form and this shows everything
or you can have your form open up this folder each time the user is on the form ..
does this help

G

 

[samg2013]

Thanks guys, I ended up getting working with the following:

Dim f   As Object
Set f = Application.FileDialog(3)
 
With f
    ' Allow Mulitple Selections
    .AllowMultiSelect = False
 
    ' Set the title of the dialog box. '
    .Title = "Please select file to link"
 
    ' Clear out the current filters, and add our own.'
    .Filters.Clear
    .Filters.Add "Excel 2003 File", "*.XLS"
    .Filters.Add "Excel 2010 File", "*.XLSX"
    .Filters.Add "All Files", "*.*"
End With  '--- f
' Call .Show and show the dialog. If the method returns True, the user picked at least one file.
' If the method returns False, the user clicked Cancel.
If f.Show Then
    MsgBox f.SelectedItems.Count & " file selected."
 
 
 
    ' Display the full path to each file that was selected
    Dim i As Integer
    For i = 1 To f.SelectedItems.Count
        MsgBox f.SelectedItems(i)
        Forms!form1!doc = f.SelectedItems(i)
    Next i
End If
Call replacehyper
End Sub

The next problem, i need to replace the drive letter such as C:\ to the unc drive name such as \\DRIVE1\DRIVE\.

I'm using this:

Private Sub replacehyper()
Dim docResult As String
docResult = Replace([doc], "C:\", "\\DRIVE1\DRIVE\ )
 Forms!form1!doc = docResult
End Sub

This only works if the drive is 'C'. Is there anyway to use a wildcard for any drive assignment? such as "*:\"?

Thanks for your help.

 

[MarkK]

    Dim i As Integer
    For i = 1 To f.SelectedItems.Count
        MsgBox f.SelectedItems(i)
        Forms!form1!doc = f.SelectedItems(i)
    Next i

If there are mutiple selected items here, you are just going to keep overwriting them until only the last one is stored. See what I mean? Set f.AllowMultiSelect to false, or change how this loop works.

Also, I don't understand how a wildcard drive assignment will work.

 

[gemma-the-husky]

I tend to just store a text field, with the file target. Add a browse button to let them change the target.

on a button click you can do

application.followhyperlink "txtvalue"