Obtaining File location and File Name RRS feed

  • Question

  • I use the following to obtain the File Location and File names.  However, it seems that the file locations and Names are being changed or shortened with this "~" in them.  any way I can obtain a actual file location and name?  Why is this occurring?

    Private Sub FileLink_Click()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    Dim rst As New ADODB.Recordset
    With fd
        .Title = "Please Select File(s)"
        If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
        rst.Open "Documents", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
            With rst
                'Add new record to end of Recordset object
                'Add Data
                ![Doc link] = vrtSelectedItem
                ![Doc Title] = GetFileName(vrtSelectedItem)
                'Save Changes
            End With
            Next vrtSelectedItem
            MsgBox "You Cancelled the link.  Docments not linked"
            End If
    End With
    Set rst = Nothing
    Set fd = Nothing
        'Exit sub
        'MsgBox Err.Number
    End Sub

    Monday, November 2, 2020 10:26 PM

All replies

  • Please supply an example (or two) of the complete file path and what your code gives.


    a) what is the code used in the GetFileName function

    b) why do you have a Requery line and what is it meant to be requerying

    c) you have commented out the error handler code. What happen if you reinstate it ...together with a On Error GoTo HandleError line

    d) any reason why you are using ADODB rather than DAO?

    • Edited by isladogs52 Monday, November 2, 2020 10:44 PM
    Monday, November 2, 2020 10:42 PM
  • b._ The Requery refreshes the form to update the Document List

    C.)No errors occur when reinstated.

    d.) No reason, I was provide this code to work with.  I'd loved to see the DAO example.


    Function GetFileName(xFileLocation As Variant)
    On Error GoTo Err_Handler
    GetFileName = Right(xFileLocation, Len(xFileLocation) - InStrRev(xFileLocation, "\"))
        Exit Function
        MsgBox "The Following error has occurred." & vbCrLf & vbCrLf & _
            "Error Source:  GetFileName" & vbCrLf & _
            "Error Description:  " & Err.Description, vbCritical, "An Error has Occurred!"
        GoTo Exit_Err_Handler
    End Function

    Tuesday, November 3, 2020 12:21 AM
  • OK - so as requested, please provide a couple of examples of the complete file path and what your code gives in each case
    Tuesday, November 3, 2020 12:48 AM
  • Those are "short" filenames - having a maximum length of 8-dot-3 characters.

    They are totally valid, so you can safely store them.

    But why you receive them, I can't tell; normally the file dialogue returns the long filenames.

    Gustav Brock

    Tuesday, November 3, 2020 9:53 AM