Software

Ask something
Question Locked

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

0 helpful
2 replies

by curtishearn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

2 replies

by Former member

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
this

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:
http://www.pcreview.co.uk/forums/thread-962429.php

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

This discussion is closed to new replies.