Questions

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

+
0 Votes
Locked

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

curtishearn
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
    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.

  • +
    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
    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.