1

I want to use a file open dialog to extract a file pathway (or open the file if easier)

Is it possible to set the dialog so that it will not open a file if a file-name is double-clicked? What I want to avoid is if the user double-clicks a file name but that file is already open then a further alert appears.

Or, alternatively, it would work if I set things up so that a read-only version of the file is opened when the user clicks the dialog's OPEN button or double-clicks a file name - is this an easier approach? In this case do I use the dialog's Execute method ?

Private Function FindFilePath() As Boolean

Dim selectedMultiFiles As Boolean 
Dim fd As FileDialog 
Dim objfl As Variant

Set fd = Excel.Application.FileDialog(msoFileDialogOpen)

Dim myTxt As String 
With fd
    .Filters.Add "Excel Files", "*.xlsx;*.xlsm", 1
    .AllowMultiSelect = False
    .Title = "Choose the file with the target table"
    .InitialView = msoFileDialogViewDetails
    If .Show = -1 Then
        myTxt = .SelectedItems.Item(1)
        fFileName = myTxt
        FindFilePath = True
    Else
        myTxt = "Nothing was selected"
        FindFilePath = False
    End If
    On Error Resume Next End With

txBoxFilePath.Text = myTxt

End Function
Community
  • 1
  • 1
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Let the user double click. You could always check if the file is open or not. See [THIS](http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba/9373914#9373914) – Siddharth Rout Oct 10 '13 at 13:10

1 Answers1

1

I am not sure how much this would mess your current project up but are you aware of

Dim getPath As Variant
getPath = Application.GetOpenFilename
Debug.Print getPath

where getPath will literally store the path to whatever file the user chose.

It will not open the file automatically unless you actually Set getPath = App..

You can open the file later in your code performing checks for the file being already open or just opening it read-only like you mentioned.

  • strange how I can right-click and select Open and the file opens....but does avoid the double-click route – whytheq Oct 10 '13 at 13:50
  • @whytheq so are you asking to temporarily disable right click button when selecting the file? (*ie. in Windows*) check [`this out`](http://www.raymond.cc/blog/restrict-or-disable-mouse-right-click-at-desktop-and-explorer/) –  Oct 10 '13 at 13:54
  • keeping in mind that you can modify the registry from VBA but it's not a recommended solution. It's not strange that you can right click the file and open it because the GetOpenFile uses system resources to display the dialog box. It works exactly as Windows Explorer window. Whats different is how you assign the returned object in VBA code. –  Oct 10 '13 at 14:00