title: Excel VBA - Select Ok Popup/Dialog Box
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>I have tried inserting Application.DisplayAlerts = False at various points in the code but it doesn't prevent that dialog.</p>
  
  <p>Any help/ suggestion is greatly appreciated.</p>
  
  <p>Thanks</p>
  
  <p><strong>Edit</strong></p>
  
  <p>Originally I had:</p>
  
  <p><code>Cells(r, 5).Formula = "='" &amp; Left(File.Path, InStr(File.Path, File.Name) - 1) &amp; "[" &amp; File.Name &amp; "]Summary'!$D$3"</code></p>
  
  <p>...producing the following cell formula:</p>
  
  <pre><code>='C:\Documents and Settings\[TEST]Summary'!$D$3
  </code></pre>