View original (French)

Adding a browse button in VBA Excel

Solved
schont Posted messages 33 Status Member -  
 Gab -
Quel code dois-je utiliser pour ajouter un bouton parcourir dans une userform ?
Je voudrais ajouter un bouton de commande qui utilise la fonction parcourir et lorsque que le fichier désiré est sélection son chemin s'affiche dans une zone de texte se trouvant dans la même userform.
C'est pour lier une référence à fichier pdf.

Si quelqu'un a une idée ...

Merci
Configuration: Windows XP Internet Explorer 6.0 excel 2003

4 answers

  1.
    Papou93 Posted messages 146 Registration date   Status Member Last intervention   59
     
    Hello schont,

    I think this is what you are looking for: 

     Private Sub CommandButton1_Click() With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False .Show UserForm1.TextBox1.Text = .SelectedItems(1) End With End Sub


    Adapt the control names to your application.

    Regards.
    11
    1. schont Posted messages 33 Status Member 10
       
      Thank you, it's exactly what I wanted. I was struggling with:

      Private Sub CommandButton3_Click()
      FileToOpen = Application _
      .GetOpenFilename("PDF Files (*.pdf), *.pdf", 1, "Browse...")
      If FileToOpen = False Then
      MsgBox "Operation canceled", vbExclamation
      Exit Sub
      End If
      ' is something missing?!
      End Sub

      Now, a little question: the file path is copied to my TextBox11 and when I click on a "complete" button, the value of my TextBox is copied to a cell, the code is:

      Sheets("Sheet1").Cells(Lg, "I").Value = UserForm1.TextBox11.Value

      The file path displays correctly, but not as a link, which is convenient for opening it with a single click.

      Is there an option to check in Excel to transform a path into a link (I believe Word does it automatically when you type a web address), or does it have to be done through VBA?

      Anyway, kudos and thank you for the speed and efficiency!
      0
      1. Papou93 Posted messages 146 Registration date   Status Member Last intervention   59 > schont Posted messages 33 Status Member
         
        You just need to add this code after the TextBox is filled: 

        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=UserForm1.TextBox1.Text


        You can replace Selection with a fixed cell address.

        Best regards
        0
      2. schont Posted messages 33 Status Member 10 > Papou93 Posted messages 146 Registration date   Status Member Last intervention  
         
        Thank you once again!
        0
    2. Gab
       
      Thank you, guys!
      0
  2. schont Posted messages 33 Status Member 10
     
    Hello,

    The .GetOpenFilename method does not seem to offer that kind of option.

    However, I think we can simplify the code a bit: 

    Dim file_name As String file_name = Application.GetOpenFilename file_name = Mid(file_name, InStrRev(file_name, "\") + 1, InStrRev(file_name, ".") - InStrRev(file_name, "\") - 1) Range("a1") = file_name


    Best regards,
    2
    1. Biorix
       
      Thank you, that works perfectly!
      0
  3. schont Posted messages 33 Status Member 10
     
    I just noticed that when you click on the browse button and then instead of clicking "open" you click "cancel," an error returns "runtime error '5'" and debugging is proposed.
    I tried with 
    If FileToOpen = False Then MsgBox "Operation cancelled", vbExclamation Exit Sub End If


    but with that, even when I click "open," the message box opens and displays the message.

    Thank you for helping me solve this problem.
    0
    1. Papou93 Posted messages 146 Registration date   Status Member Last intervention   59
       
      Good evening, schont,

      I don't understand why this error occurs, but a quick and simple solution is to add an error handling line at the beginning of the procedure; 
      Private Sub CommandButton1_Click() On Error Resume Next With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False .Show UserForm1.TextBox1.Text = .SelectedItems(1) End With End Sub

      There, that’s fixed!

      Best regards
      0
      1. schont Posted messages 33 Status Member 10 > Papou93 Posted messages 146 Registration date   Status Member Last intervention  
         
        You're welcome!
        0
    2. logical
       
      Response late but may interest some, replace the line:
      .Show
      with
      If .Show = 0 Then Exit Sub
      0
  4. Biorix
     
    Bonjour,

    Is it possible to use the browse button but only retrieve the file name without the full path or the .xlsx extension?

    That is to say: "myfile" instead of "C:\folder\myfile.xlsx"

    Because so far I have only been able to do a complicated manipulation to isolate the name:

    Dim file As String
    Dim file1 As String
    Dim file2 As String
    Dim file3 As String
    Dim name As String

    file1 = Range("a1").Value

    For i = Len(file1) - 5 To 1 Step -1
    file2 = Mid(file1, i, 1)
    If file2 = "\" Then
    GoTo file_name
    End If
    Next i
    file_name: file3 = Mid(file1, i + 1)

    file = Left(file3, Len(file3) - 5)
    Range("a1") = file

    Thank you
    0