title: Access VBA to open, edit and save Excel doc
body: <p>I have an Access DB to open, edit and save an Excel doc that works fine the first time run but if I try to alter more than one file (or the same file twice) it fails with "Run-time error '1004': Method 'Cells' of object '_Global' failed"</p>
  
  <p>If I close the DB then re-open it, it again works fine for the first file altered.</p>
  
  <p>Although I am not new to VBA I would say that I am a novice.  Here is a snippit of the code I am using:</p>
  
  <pre><code>    Code:
          'Open spreadsheet and make it visible
          Set xl = CreateObject("Excel.Application")
          strInputFile = varItem
          xl.Workbooks.Open strInputFile
          xl.Visible = True
  
          'Trying to get row count here but not working yet
          'Set myRange = xl.Sheets("Sheet1").Range("C:C")
          'lRowCount = Excel.Application.WorksheetFunction.CountA("Sheet1").Range("C:C")
          'lRowCount = xl.WorksheetFunction.CountA(Worksheets("Sheet1").Cells(C, C))
          'Debug.Print lRowCount
          'strMyRange = "C:C"
          'lRowCount = xl.WorksheetFunction.CountA(strMyRange)
          'Debug.Print lRowCount
          'lRowCount = Excel.Application.WorksheetFunction.CountA(Workbooks(strInputFile).Sheets("Sheet1").Range("C:C"))
          'Debug.Print lRowCount
  
          'Make the changes
          j = 0
          If Left(strFile, 4) = "xxxx" Then 
              myPath = "\\a\path\for\xxxx"
              If InStr(1, strFile, "IQ") Then
                  For i = 1 To 500 'Row count not working yet
                      If InStr(1, Cells(i, "C").Value, myVariable) &gt; 0 Then
                          Cells(i, "B") = "New Value"
                          j = j + 1
                      End If
                  Next
              End If
          End If
  
  'Clean up
  xl.Quit
  Set xl = Nothing
  Set objInputFile = Nothing
  </code></pre>