locked
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
                .AddNew
                'Add Data
                ![Doc link] = vrtSelectedItem
                ![Doc Title] = GetFileName(vrtSelectedItem)
                'Save Changes
                .Update
            End With
            
            rst.Close
            
            Next vrtSelectedItem
            
            Else
            
            MsgBox "You Cancelled the link.  Docments not linked"
            
            End If
            
    End With
    
    Set rst = Nothing
    Set fd = Nothing
    
    Requery
    
    'ExitHere:
    
        'Exit sub
        
    'HandleError:
        
        '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.

    Also

    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.

    a.)

    Function GetFileName(xFileLocation As Variant)
    On Error GoTo Err_Handler
    
    GetFileName = Right(xFileLocation, Len(xFileLocation) - InStrRev(xFileLocation, "\"))
    
    Exit_Err_Handler:
        Exit Function
        
    Err_Handler:
        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