VBA Excel File Open Prompt Cancel Error

excel vba error-handling file-processing
grimchamp picture grimchamp · Apr 9, 2013 · Viewed 20.8k times · Source

So I am using a file open prompt to gather a filename. I then open this file in a background instance, parse information to a dictionary and close the file. This works fine. The code for this is:

Application.FileDialog(msoFileDialogOpen).Show
sFullName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

If the user presses cancel, which is obviously a feasible scenario, I get the following error:

Invalid procedure call or argument

I have tried to change the 'gather' line to:

If Application.FileDialog(msoFileDialogOpen).SelectedItems(1) Then sFullName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

However this still brings up the error. Even disabling alerts brings up an error or '400'. Any help on how to make this popup or handle it would be greatly appreciated.

Answer

Alex K. picture Alex K. · Apr 9, 2013

You need check the bounds to determine if anything was selected

with Application.FileDialog(msoFileDialogOpen)

    .Show

    if (.SelectedItems.Count = 0) Then
        '// dialog dismissed with no selection
    else
        sFullName = .SelectedItems(1)
    end if
end with

Related questions

Handle Error 9 when there is an Empty Array

I am writing a script that will loop through an Excel spreadsheet and find if there are duplicates of selected cells. If there are duplicates then the function will return an array of which rows are duplicates and create a …

Read More
Handle cancellation of InputBox to select range

I have the following piece of code: dim selectRange as Range Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8) When a user chooses Cancel the InputBox prompt, it returns error of Object not set. I have tried to use a …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More