Access VBA FileDialog Execute Error (Access 2007)

When I run the code in the snippet, the .Execute line (line 9) gives the error:

Error:-2147467259  Description:You already have the database open.

How can I fix this?
Public Function TestFileDialog()
' set up error handling
On Error GoTo ErrorHandler
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        .InitialFileName = Environ("USERPROFILE") & "\Desktop\*.*"
        .Show
        .Execute
    End With
ExitHere:
    Exit Function
ErrorHandler:
    Debug.Print "Error:" & Err.Number & "  Description:" & Err.Description
    Resume ExitHere
End Function

Open in new window

LVL 16
Chuck WoodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chuck WoodAuthor Commented:
Should be: When I run the code in the snippet, the .Execute line (line 9) gives the error:
0
Rey Obrero (Capricorn1)Commented:
change this

 .InitialFileName = Environ("USERPROFILE") & "\Desktop\*.*"

to

 .InitialFileName = Environ("USERPROFILE") & "\Desktop\"

or

 .InitialFileName = Environ("USERPROFILE") & "\Desktop"
0
Rey Obrero (Capricorn1)Commented:




.Filters.Add "All Files", "*.*"
Public Function TestFileDialog()
' set up error handling
On Error GoTo ErrorHandler
    Dim fd As FileDialog, res as integer
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        .InitialFileName = Environ("USERPROFILE") & "\Desktop"
        .Filters.Add "All Files", "*.*"        
        res =.Show
        '.Execute
    End With
ExitHere:
    Exit Function
ErrorHandler:
    Debug.Print "Error:" & Err.Number & "  Description:" & Err.Description
    Resume ExitHere
End Function

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Chuck WoodAuthor Commented:
Thank you for your responses. I have tried every combination of what you proposed and I get the same error unless I comment out the .Execute. The trouble is: without the .Execute, the file will not open. I am running Vista but I do not see how that would cause this. Any other suggestions?
0
Rey Obrero (Capricorn1)Commented:
what? are you opening a file with file dialog?

you have to get the path and name of file and use Shell to open the file
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck WoodAuthor Commented:
According to the FileDialog Object Members help, the Show Method:

Displays a file dialog box and returns a Long indicating whether the user pressed the Action button (-1) or the Cancel button (0). When you call the Show method, no more code executes until the user dismisses the file dialog box. In the case of Open and SaveAs dialog boxes, use the Execute method right after the Show method to carry out the user's action.

I tried it out in Excel 2007 and it works, BUT it only opens Excel files and things that Excel can open as Excel, like text files. So, it is good for Excel and Word (to open Excel and Word files) but not Access. I think MS missed the boat on this object.
0
Chuck WoodAuthor Commented:
Thanks, capricorn1, you did not tell me how to make it work but you pointed me to why it did not work in Access and what (somewhat limited) use I could make of the FileDialog object.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.