Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2016
    Posts
    19

    Unanswered: FileDialog to a File Name with a "#" in the Folder Path or File Name

    I have posted this issue on another forum at the link below but have not been able to resolve the issue.

    https://www.access-programmers.co.uk...d.php?t=293224

    I am using the below code with a command button to browse to a file and insert it's path and file name in a textbox on a form. However, if the file or folder name contains a "#", it is truncating the file name just prior to the #. I believe the full name is there, it's just not showing it. The goal is to be able to browse to file, insert the path and file name whether it be a .docx, .xls, .pdf or for the users to be able to type a web address in the textbox and then for the field when clicked to follow the hyperlink and open the file or web address. The textbox is a hyperlink data type in the table and the "Is Hyperlink" property in textbox in the form is set to yes. Any help would be greatly appreciated.

    Code:
    Private Sub cmdInsertFile_Click()
    
    Dim f As Object
        Dim strFile As String
        Dim strFolder As String
        Dim varItem As Variant
    
        Set f = Application.FileDialog(3)
        f.AllowMultiSelect = False
          
        
        If f.Show Then
            For Each varItem In f.SelectedItems
                strFile = Dir(varItem)
                strFolder = Left(varItem, Len(varItem) - Len(strFile))
                Me.Location = strFolder & strFile
                MsgBox strFolder & strFile
            Next
        End If
        Set f = Nothing
        
        DoCmd.RefreshRecord
        
        Forms!frmRescEdit!Type.SetFocus
        
    End Sub

  2. #2
    Join Date
    Dec 2016
    Posts
    19
    As a follow-up I have attached a sample database with the code.

    Database2.zip

  3. #3
    Join Date
    Apr 2017
    Posts
    10

    FileDialog to a File Name with a "#" in the Folder Path or File Name

    Since I'd never run across this problem before, I ran a test on my own code to see if there was some problem when grabbing a file name with a # character. No problem on my end.
    So, below is my GetFile Module. Withing the "Object" code call the Module to send back the file name. My object(Text box) is called "FilePath".
    Also, No need to capture the file name separately from the file path. This will capture the entire file path with the file.

    Private Sub FilePath_DblClick(Cancel As Integer)
    Dim NewPath As String
    NewPath = getFileName(FilePath) ' Module-GetFileName Function
    'Other Code...
    End Sub

    In your Module enter this function:

    Function getFileName(FilePath) As String
    Dim fDialog As Object

    Set fDialog = Application.FileDialog(3) ' 3 = FilePicker Dialog
    Dim varFile As Variant
    With fDialog
    .AllowMultiSelect = False
    .Title = "Select File Name:"
    .InitialFileName = FilePath
    If .Show = True Then
    For Each varFile In .SelectedItems
    getFileName = varFile
    Next
    End If
    End With
    End Function

    Hope this helps.

  4. #4
    Join Date
    Dec 2016
    Posts
    19
    Thank you for your assistance and I apologize in advanced if my questions are dumb but I have been working this issue since yesterday and I'm exhausted. I need to clarify a few things. Does it need to be a DblClick event? Can I use OnClick?

    If the textbox that I want the file name and path to be inserted in is named "Location" I need to change the object code to the below, correct?

    Code:
    Private Sub Location_DblClick(Cancel As Integer)
    Dim NewPath As String
    NewPath = getFileName(Location) ' Module-GetFileName Function
    'Other Code...
    End Sub

    Do I name the Function "getFileName", "getFileName(FilePath)" or "getFileName(Location)"

  5. #5
    Join Date
    Apr 2017
    Posts
    10

    FileDialog to a File Name with a "#" in the Folder Path or File Name

    1. Does NOT have to be a DblClick event.
    2. It would be best to Right click the field (Location) then select Properties. Place the cursor the event (Click or Double Click) click the 3 dots at the right and select code builder
    This will write the code to the appropriate object and let the object know its there.
    3. To keep things simple... rename FilePath to Location at each instance.

    Once you have this module working. It can be called from anywhere in your program and for future programs

    Trial and Error can be frustrating but also very helpful.....

  6. #6
    Join Date
    Dec 2016
    Posts
    19
    It's not working for me. Below is the code tweaked to my situation. What am I doing wrong?

    I named the module "GetFileName(Location)"

    Code:
    Function getFileName(Location) As String
        Dim fDialog As Object
        Set fDialog = Application.FileDialog(3) ' 3 = FilePicker Dialog
        Dim varFile As Variant
        With fDialog
        .AllowMultiSelect = False
        .Title = "Select File Name:"
        .InitialFileName = Location
        If .Show = True Then
        For Each varFile In .SelectedItems
        getFileName = varFile
        Next
        End If
        End With
    End Function

    Code:
    Private Sub cmdInsertFile_Click()
    
        Dim NewPath As String
        NewPath = getFileName(Location) 
    
    End Sub
    When I click the command button I receive the error "Run-time error '13': Type mismatch" and it stops on this line of code

    Code:
    .InitialFileName = Location

  7. #7
    Join Date
    Apr 2017
    Posts
    10

    FileDialog to a File Name with a "#" in the Folder Path or File Name

    When I click the command button I receive the error "Run-time error '13': Type mismatch" and it stops on this line of code
    Code:
    .InitialFileName = Location


    The GetFile Function will take in information sent to it as well as from it by way of the "Location" Variable. ie, GetFileName(Location)
    So we may be trying to set the InitialFileName with inappropriate information.
    "Location" could have a Null or incorrect value.

    Prior to Clicking (Double Clicking) Enter a path such as C:\ in the Location Field so that it has a usable path

    If yhis doesn't work for any reason, I'll rewrite the programming for you and send you complete and working code using your LOCATION field tomorrow... just having a busy day today.

  8. #8
    Join Date
    Dec 2016
    Posts
    19
    I entered C:\ in the location field and then clicked the command button. I didn't receive any error messages and nothing was inserted into the location field.

    Thanks so much. I really appreciate it.

  9. #9
    Join Date
    Apr 2017
    Posts
    10

    FileDialog to a File Name with a "#" in the Folder Path or File Name

    OBJECT CODE REVISED
    Private Sub Location_DblClick(Cancel As Integer)
    NewPath = getFileName(Me.Location)
    Me.Location = NewPath
    End Sub




    MODULE CODE - Back to Original

    Function getFileName(FilePath) As String
    Dim fDialog As Object

    Set fDialog = Application.FileDialog(3) ' 3 = FilePicker Dialog
    Dim varFile As Variant
    With fDialog
    .AllowMultiSelect = False
    .Title = "Select File Name:"
    .InitialFileName = FilePath
    If .Show = True Then
    For Each varFile In .SelectedItems
    getFileName = varFile
    Next
    End If
    End With
    End Function

    TESTED AND RUNNING - ALSO TESTED WITH YOUR ORIGINAL # PROBLEM

  10. #10
    Join Date
    Dec 2016
    Posts
    19
    I think we have some confusion. My form has a command button titled "cmdInsertFile" that I want to be able to click to browse to a file, click the file for it's location to insert into the "Location" field.

    The code you sent has the user double clicking the location field. However, I did try your method and received the error "Compile error: Expected variable or procedure, not module".

    Since the code is working for, could you possibly utilize the sample database I included above and insert the module code and the onclick code for the cmdFileInsert button and return it to me so I can see what I'm doing wrong.

  11. #11
    Join Date
    Apr 2017
    Posts
    10

    Database2 Revised Attached

    Revised Database2 Attached.
    I quickly understood the problem once I reviewed your database.
    The hyperlink field is unable to accept the #.

    I revised the database to use a text field instead.
    Opens the file dialog with the buttom and inserts the file selected.
    Double click the filed to open the file.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •