Access 2003 Error: Method "Cells" of object '_Global' Failed

0 Votes

Access 2003 Error: Method "Cells" of object '_Global' Failed

I've written an Access app that exports data to Excel and then formats it into a personal financial statement. The first time I run it, it works great. The second time, I get the error: "Method 'Cells' of object '_Global' Failed" at a line which reads as follows:

appExcel.ActiveSheet.Range(Cells(RowNo, 1), Cells(RowNo, 8)).Select

Any ideas? It seems to generate this error only after I've run the report previously. If I close out of Access and get back in, it works fine the first time but bugs out again the second.
0 Votes

The problem is that you haven't qualified the Cells reference with the sheet to which it applies, so VB is creating a whole new instance
of Excel for you! You must *always* fully-qualify any call into the Excel object model with at least the Excel application object. Try

ExcelSht.Range(ExcelSht.Cells(row, 7), ExcelSht.Cells(row, i - 1)).Select
ExcelSht.Cells(row, i).Formula = "=sum(" & Selection.AddressLocal & ")"

It's not a good idea to select things in Excel, so you could use instead:

With ExcelSht
.Cells(row, i).Formula = "=SUM(" & .Range(.Cells(row, 7), .Cells(row, i - 1)).Address & ")"
End With

Note that I've used a With block to avoid the repeated references to ExcelSht in the code (just for tidiness) and used .Address instead
of .AddressLocal, as the latter is likely to give you errors in non-English versions of Excel.

Note also that the code above will result in absolute cell references. If you're not bothered about whether they're absolute or
relative, an easier formula might be:

ExcelSht.Cells(row, i).FormulaR1C1 = "=SUM(RC7:RC[-1])"
Copied from:

Please post back if you have any more problems or questions.