CODEWITHSUNDEEP
excelvba
kavmeister
kavmeister

Reputation: 87

Excel VBA - Select Ok Popup/Dialog Box

I've written a macro for Excel 2010 which indexes a folder for any containing files and subfolders. The purpose of this is to manage the extraction of a particular detail from a folder directory containing hundreds of files only some of which are Excel files and of those only some of which are relevant to this detail.

The macro provides a few basic details and hyperlinks to the files (including xls, doc, pdf etc). The vba code then inserts a cell formula to extract a single cell value from a particular location of the workbooks (without opening them) where there is an Excel file and produces a #Ref! error otherwise. Since the cell formula is created through FileItem.Path and text manipulation, the formula always references the "Summary" sheet as is appropriate for the meaningful files.

The problem is that the remaining Excel files do not have a worksheet with this name which prompts Excel to deliver a popup dialog 'Select Sheet' for the user to manually choose from the options. I need a way to manage this in the vba code. I can manage a number of potential outcomes including selecting OK to choose the 1st option every time, cancelling and ignoring the request, skipping those instances etc but I cannot have the macro repeatedly interrupted for user input.

I have tried inserting Application.DisplayAlerts = False at various points in the code but it doesn't prevent that dialog.

Any help/ suggestion is greatly appreciated.

Thanks

Edit

Originally I had:

Cells(r, 5).Formula = "='" & Left(File.Path, InStr(File.Path, File.Name) - 1) & "[" & File.Name & "]Summary'!$D$3"

...producing the following cell formula:

='C:\Documents and Settings\[TEST]Summary'!$D$3

Upvotes: 0

Views: 5260

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

It does indeed seem that you can't disable this "invalid reference" pop-up box (please correct me if I am wrong someone!), however, what you could do is generate your formula for the reference to your "Summary" sheet in VBA (as you are already doing), but evaluate it in VBA before pasting the actual formula so that if the formula returned fine without an error then paste it, otherwise you can perform some other action instead.

For example you have at the moment:

if FileType <> "XLS" then
    myCellFormula = "#Ref!"
else
    myCellFormula = "[<Target File Name>]Summary!A1"
endif

However, as you know trying to put in a reference to a non-existent sheet will cause excel to cough. What you can do is check for this error in VBA, for example:

On Error Resume Next

dim dummy as variant

if FileType <> "XLS" then
    myCellFormula = "#Ref!"
else
    dummy = Application.Range("[<Target File Name>]Summary!A1").Value

    if not isempty(dummy) then
        myCellFormula = "[<Target File Name>]Summary!A1"
    else
        <alternative action>
    endif
endif

You could also do this with an error handler, it does depend on whether you wish to skip these non-existent references (so <alternative action> would be nothing).

Edit based on your latest reply, you could change the code to something like this:

On Error Resume Next

dim dummy as variant
dim targetFileFormula as string
dim lastSlashPos as long

lastSlashPos = InStrRev(fileitem.Path, "\", , vbBinaryCompare)
targetFileFormula = "'" & Left(fileitem.Path, lastSlashPos) & "[" & Right(fileitem.Path, Len(fileitem.Path) - lastSlashPos) & "]Summary'!$D$3"

if FileType <> "XLS" then
    myCellFormula = "#Ref!"
else
    dummy = Application.Range(targetFileFormula).Value

    if not isempty(dummy) then
        myCellFormula = targetFileFormula
    else
        <alternative action>
    endif
endif

You can then tailor this to your needs.

Upvotes: 1

Related Questions