title: Find next available row
body: <p>I have a VBA script in Outlook that attempts to grab information from email items and write it to an Excel file.</p>
  <p>I included my full script at the end. Here is a quick outline, where I include the parts that I think might need some work.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub Output2Excel()
      Dim xlApp As Object
      Dim xlWkBk As Object
      Dim xlSheet As Object
  
      ' Setup the Excel Application
      Set xlApp = Application.CreateObject(&quot;Excel.Application&quot;)
      Set xlWkBk = xlApp.Workbooks.Open(PathName &amp; FileName, , False) ' Open the Excel file to be updated
      Set xlSheet = xlWkBk.Worksheets(1)
  
      ' Loop over all the olMail items in FolderTgt, which is a MAPIFolder type
      RowNext = xlSheet.Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row + 1 ' &lt;- This line highlighted by debugger (see below)
      ' Write stuff to Excel like
      ' For
          xlSheet.Cells(RowNext , Col).Value = [Whatever Item I want out of FolderTgt]
          RowNext = RowNext + 1
      ' Next
  
      ' Done with the loop, now save the file and close things down
      xlWkBk.Save
      Set xlSheet = Nothing
      xlWkBk.Close
      Set xlWkBk = Nothing
      xlApp.Quit
      Set xlApp = Nothing
          
      Debug.Print &quot;All Done&quot;
  End Sub
  </code></pre>
  <p>When I run this script, it updates my excel file correctly, producing results like:</p>
  <pre><code>+ - + ------- + --------------- + -------- + - +
  | 2 | Sender1 | SomeSubject     | 04/13/17 | 0 |
  | 3 | Sender2 | AnotherSubject  | 04/13/17 | 0 | 
  | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 |
  + - + ------- + --------------- + -------- + - +
  </code></pre>
  <p>I can even run it multiple times and it appends to the file without issue:</p>
  <pre><code>+ - + ------- + --------------- + -------- + - +
  | 2 | Sender1 | SomeSubject     | 04/13/17 | 0 |
  | 3 | Sender2 | AnotherSubject  | 04/13/17 | 0 | 
  | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 |
  | 2 | Sender1 | SomeSubject     | 04/13/17 | 0 |
  | 3 | Sender2 | AnotherSubject  | 04/13/17 | 0 | 
  | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 |
  | 2 | Sender1 | SomeSubject     | 04/13/17 | 0 |
  | 3 | Sender2 | AnotherSubject  | 04/13/17 | 0 | 
  | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 |
  + - + ------- + --------------- + -------- + - +
  </code></pre>
  <p><strong>Here's the problem:</strong></p>
  <p>I open the Excel file to look at the results. I close it without any modifications. Then, I try to run the script again in VBA, and I get the following error:</p>
  <blockquote>
  <p>Run-time error '1004':<br />
  Method 'Rows' of object '_Global' failed</p>
  </blockquote>
  <p>The debugger highlights the line</p>
  <pre><code>RowNext = xlSheet.Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row + 1
  </code></pre>
  <p>This error happens only when I open the Excel file to check the results. I think the VBA script might be opening and closing the file incorrectly, but the resources I've used indicate that this is the right way to do it.</p>
  <hr />
  <p>More detailed script:</p>
  <pre class="lang-vb prettyprint-override"><code>Sub Output2Excel()
  
      Dim FolderNameTgt As String
      Dim PathName As String
      Dim FileName As String
  
      Dim FolderTgt As MAPIFolder
  
      Dim xlApp As Object
      Dim xlWkBk As Object
      Dim xlSheet As Object
  
      Dim RowNext As Integer
      Dim InxItemCrnt As Integer
      Dim FolderItem As Object
  
      ' Outlook folder, computer directory, and excel file involved in the reading and writing
      FolderNameTgt = &quot;MyUserId|Testing VBA&quot;
      PathName = &quot;N:\Outlook Excel VBA\&quot;
      FileName = &quot;Book1.xls&quot;
  
      ' Locate the Folder in Outlook. I've left out some of the details here because this part works fine
      Call FindFolder(FolderTgt, FolderNameTgt, &quot;|&quot;)
      If FolderTgt Is Nothing Then
          Debug.Print FolderNameTgt &amp; &quot; not found&quot;
          Exit Sub
      End If
  
      ' Setup the Excel Application
      Set xlApp = Application.CreateObject(&quot;Excel.Application&quot;)
      Set xlWkBk = xlApp.Workbooks.Open(PathName &amp; FileName, , False)    
      Set xlSheet = xlWkBk.Worksheets(1)
  
      ' Loop over all the items in FolderTgt
      RowNext = xlSheet.Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row + 1
      For InxItemCrnt = 1 To FolderTgt.Items.Count
        
          ' Set and use the referenced item
          Set FolderItem = FolderTgt.Items.Item(InxItemCrnt)
  
          ' If the Item is of the olMail class, then extract information and write it to excel
          If FolderItemClass = olMail Then
              xlSheet.Cells(RowNext, 1).Value = RowNext
              xlSheet.Cells(RowNext, 2).Value = FolderItem.SenderName
              xlSheet.Cells(RowNext, 3).Value = FolderItem.Subject
              xlSheet.Cells(RowNext, 4).Value = FolderItem.ReceivedTime
              xlSheet.Cells(RowNext, 4).NumberFormat = &quot;mm/dd/yy&quot;
              xlSheet.Cells(RowNext, 5).Value = FolderItem.Attachments.Count
              RowNext = RowNext + 1
          End If
  
      Next InxItemCrnt
          
      ' Done with the loop, now save the file and close things down
      xlWkBk.Save 'FileName:=PathName &amp; FileName
      Set xlSheet = Nothing
      xlWkBk.Close
      Set xlWkBk = Nothing
      xlApp.Quit
      Set xlApp = Nothing
          
      Debug.Print &quot;All Done&quot;
  End Sub
  </code></pre>