Runtime Error 1004: Method ‘Cells’ of Object ‘_Global’ failed

[ielamrani]

Hi,
I am getting this error when I try to export to an excel sheet. When I click on a button to export the first time it's fine, I rename the exported excel sheet and I try to export it again and I get the error: Runtime Error 1004: Method ‘Cells’ of Object ‘_Global’ failed

It highlight this line:

Range("A1:L1").Select

sorry the code behind the button is long:

.
    Dim stDocName As String
 
    stDocName = "QFinal4"
    DoCmd.OutputTo acReport, stDocName
 
    Dim xcelwb As Excel.Workbook
    Dim xcelapp As Excel.Application
    Dim Sheet As Excel.Worksheet
 
    Set xcelapp = New Excel.Application
    Set xcelwb = xcelapp.Workbooks.Open("QFinal4.xls")
    Set Sheet = xcelapp.ActiveWorkbook.Sheets(1)
 
       'format the excel data
       With Sheet
 
    Range("A1:L1").Select
 
    Selection.Font.Bold = True
    Selection.Font.ColorIndex = 54
    Selection.Interior.ColorIndex = 36
    Cells.Select
    End With
    With Selection.Font
        .Name = "Tahoma"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    Cells.EntireColumn.AutoFit
    Range("B2").Select
    Selection.NumberFormat = "m/d/yyyy"
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Rows("1:2").Select
    Selection.Insert Shift:=xlDown
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "CD"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "Policy Summary By Owner"
    Range("H1:H2").Select
    Selection.Font.ColorIndex = 0
    With Selection.Interior
        .ColorIndex = 35
        .Pattern = xlSolid
    End With
    Selection.Font.Bold = True
    Columns("H:H").EntireColumn.AutoFit
    Columns("H:H").ColumnWidth = 15
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Range("I8").Select
    Columns("H:H").ColumnWidth = 18.43
    Range("H1:I2").Select
    Selection.Interior.ColorIndex = 35
    Range("H19").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("H1:I2").Select
    Selection.Cut
    Range("F1").Select
    ActiveSheet.Paste
    Columns("H:H").EntireColumn.AutoFit
    Columns("G:G").ColumnWidth = 15.86
    Columns("G:G").ColumnWidth = 17.57
    Columns("F:F").ColumnWidth = 10.43
    Columns("F:F").ColumnWidth = 11.86
    Columns("G:G").ColumnWidth = 14.71
 Range("J4").Select
    ActiveCell.FormulaR1C1 = "Surrender Value"
    Range("J4").Select
    Selection.Font.Bold = True
    Range("J4:J5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
 
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("J4:J5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("J5").Select
    ActiveCell.FormulaR1C1 = " Date"
    Range("J7").Select
    Columns("J:J").EntireColumn.AutoFit
    Range("A5:L5").Select
    Range("L5").Activate
    Selection.Font.Underline = xlUnderlineStyleSingle
    Range("K23").Select
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("J4").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    Selection.Interior.ColorIndex = 36
    Range("J4").Select
    Selection.Font.ColorIndex = 13
    Range("J5").Select
    Selection.Copy
    Range("J4").Select
 
    Range("C4:L13").Select
    Selection.Cut
    Range("A9").Select
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("F1:F2").Select
    Columns("F:F").ColumnWidth = 38.43
    Range("G3").Select
    Selection.Copy
    Range("G1").Select
 
    Selection.Copy
    Range("G2").Select
 
    Columns("F:F").EntireColumn.AutoFit
    Range("B5").Select
    Selection.Cut
 
    Selection.ClearContents
    Range("B4").Select
    Selection.Copy
    Range("B5").Select
 
    Range("B6").Select
    Selection.Cut
    Range("A7").Select
    ActiveSheet.Paste
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "Insured Name/Date Of Birth"
    Columns("B:B").Select
    Columns("A:A").EntireColumn.AutoFit
    Range("A18").Select
 
Range("G1").Select
    Selection.Interior.ColorIndex = 2
    Range("G2").Select
    Selection.Interior.ColorIndex = 2
    Range("B5").Select
    Selection.Interior.ColorIndex = 2
    Range("H9").Select
    Selection.Font.ColorIndex = 53
    Selection.Font.Underline = xlUnderlineStyleSingle
    End With
 
    Cells.Select
    Cells.EntireRow.AutoFit
    ActiveWorkbook.Save
 
End Sub

Thanks in advance

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

[NeoPa]

If you mean line #18 (it's so much easier working with code in tags) then I suspect you need the . at the start.

.Range("A1:L1").Select

I suspect (not absolutely sure) that Range() is a shortcut for Application.Range(). As the application in this instance is Access rather than Excel, it doesn't work.

The . ensures the full and correct reference (via the sheet rather than the application but nevertheless).

[Stewart Ross]

Hi. NeoPa is right - your code would work well directly in Excel, where the likes of the references to the current Selection and Range do not need further qualification, but not when used from Access (or any other Office application) as an automation server. You will need to explicitly refer to ranges and so on for your code to work reliably. There is an explanation why in the later posts of this thread, where the original poster was running into errors for similar reasons.

When Range is referred to without qualification in Excel code it is the ActiveSheet property which is normally implied, although a range can actually include multiple worksheets. As NeoPa has said, you need to use .Range (referring to the sheet object you set in your code) explicitly within a With statement to ensure that you are referring to the correct worksheet object.

Back on the implied properties, ActiveSheet itself implies that there is a current ActiveWorkbook, another Excel property. When running Excel as an automation server there can be multiple instances of workbooks and worksheets open simultaneously within that automation instance, so trying to imply which of these applies causes all sorts of head-scratching errors in what is apparently valid code that works in Excel itself. But in Excel it is running in a single workbook instance only.

From the repeated references to the current Selection in your code I would guess that you have copied code created by the macro recorder at some stage. This works fine in Excel, but for the reasons explained above and in the linked thread it is unreliable at best when using Excel as an automation server, and can introduce very trying errors apparently at random.

To give an instance of how misleading the use of Selection can be, I used the macro recorder to record the actions involved in setting the text size for a comment box in a cell to a larger size (the default was too small) and changing the size of the comment box itself. In Excel all of this worked perfectly. In Access the self-same code worked on the first object, but not for any others. Sometimes it would fail with an error message, sometimes it did not fail but regardless it would not format the comment box on the second and subsequent worksheets. It turned out that the implicit references to the Selection were hiding that the text of comments is stored in a text frame inside a resizeable box, and once the objects involved were referred to explicitly in the automation code using the appropriate methods to resize and so on I had reliable operation and no further failures. This simple problem took about ten hours of debugging to resolve, however...

-Stewart

[NeoPa]

Nice answer Stewart :) Sounds like you have a thorough understanding of this.