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

Answer 1 / 4
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
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
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
schont Posted messages 33 Status Member 10 > Papou93 Posted messages 146 Registration date   Status Member Last intervention  
 
Thank you once again!
0
Gab
 
Thank you, guys!
0
Answer 2 / 4
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
Biorix
 
Thank you, that works perfectly!
0
Answer 3 / 4
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
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
schont Posted messages 33 Status Member 10 > Papou93 Posted messages 146 Registration date   Status Member Last intervention  
 
You're welcome!
0
logical
 
Response late but may interest some, replace the line:
.Show
with
If .Show = 0 Then Exit Sub
0
Answer 4 / 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