-5

I have found code for this but have only found code that specifies excel files not one that allows me to open any document. Basically I have a button on a work sheet that needs to open a file explorer when clicked on. Once it has been clicked on it needs to direct the user to a specific file path that contains documents of various types. The user should then be able to open any of the documents. Does anyone have a solution for this?

Thanks in advance!

Here is what I have so far from what I have found elswhere...

Private Sub Showfileexplorer_Click
Dim strFileToOpen As String
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xlsx* (*.xlsx*),")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=strFileToOpen
End If
End Sub

This code works to the point of choosing a document. I have tried picking the document I wanted (Specifically excel.xlsx files as is listed above) to open and then clicking open and it throws me an error. The thing is I don't want just excel files. If it could open any file that would be great.

Community
  • 1
  • 1
Kam
  • 7
  • 7
  • What have you tried? Please post the code you have, and note what has/hasn't worked. – BruceWayne Mar 02 '18 at 20:41
  • Did you even google or searched on SO, you might have found https://stackoverflow.com/questions/18921168/how-can-excel-vba-open-file-using-default-application – Storax Mar 02 '18 at 20:43
  • Hi I apologize I didn't post the code originally. I have edited my post. Yes I googled and searched SO before posting this question. Most of the questions are close to what I want but not spot on. I would not be surprised though if I missed something. Let me know if there is a post that can help me out with what I need and I will delete this one. Thanks for taking to time to help me out with this! – Kam Mar 02 '18 at 21:09
  • None of your guys's solutions have gotten me anywhere closer to an answer. Please let me know what I can do to get closer to a solution. I have spent an entire day searching for a solution to this but have not found one. If my question is not clear enough please let me know and I will make any needed adjustments. – Kam Mar 03 '18 at 00:03

2 Answers2

0

Someone else was able to answer my question on another forum. Credit goes to Trebor76 from Ozgrid for the user friendly solution. This is the solution that was given to me. This was pretty plug and play. 

Option Explicit
Sub Acessdocumentexplorer_Click()

    'The following has been adapted from here:
    'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-
filedialog-property-excel

    Dim lngCount As Long

    'Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show
        'Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            'MsgBox .SelectedItems(lngCount)
            CreateObject("Shell.Application").ShellExecute 
.SelectedItems(lngCount)
        Next lngCount
    End With

End Sub

Just as I needed all this code does is Simply open the file explorer when I click on the "Access Document Explorer" button that I have on my worksheet. From there it has the capability of opening any document/program I need opened/run.

Ozgrid forum Link

Kam
  • 7
  • 7
-1

You may want to use the FileDialog object 

Sub SelectFiles()
Dim FSelect As FileDialog, sFile as Variant

    Set FSelect = Application.FileDialog(msoFileDialogOpen)

    FSelect.Show

    For Each sFile In FSelect.SelectedItems
        Debug.Print sFile
        '
        ' do stuff with file, full path in sFile
        '
    Next sFile

End Sub

You can also set filter paths to let the user open files of only one or more types, let them select single or multiple files, or only folders ... all in all a very versatile beast worth exploring.

MikeD
  • 8,861
  • 2
  • 28
  • 50
  • Working on this one right now. I'll let you know if it works. Thanks! – Kam Mar 02 '18 at 21:10
  • My knowledge with excel is limited. Your code works up to the point where I select the document I would like to open. I am not sure what code I would insert into the area you have designated in green quotes. If I was to just have the file open after having it selected what would I put there? – Kam Mar 02 '18 at 21:20
  • Your solution helped but only to a point. I apologize for my lack of understanding. Could you be a little clearer about the code you included? Thanks again. – Kam Mar 03 '18 at 00:09
  • `FSelect.SelectedItems` contains a list of strings which is presented through `sFile` in the `For Each` loop.So in each path you have one full drive:\pathname\filename.ext in `sFile` and you can implement the action on that file (open/copy/move/etc.). Before `.Show`ing the dialog object you can also provide the starting path, a list of file extensions etc. – MikeD Mar 05 '18 at 09:13
  • I just wanted to say that I really appreciated the time you took in answering my question. Someone else was able to answer this for me in a way the was most suitable to my needs. Thank you so much for your patience! – Kam Mar 05 '18 at 16:40