By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,508 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,508 IT Pros & Developers. It's quick & easy.

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

P: 72
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:
Expand|Select|Wrap|Line Numbers
  1. Range("A1:L1").Select
sorry the code behind the button is long:
Expand|Select|Wrap|Line Numbers
  1. .
  2.     Dim stDocName As String
  3.  
  4.     stDocName = "QFinal4"
  5.     DoCmd.OutputTo acReport, stDocName
  6.  
  7.     Dim xcelwb As Excel.Workbook
  8.     Dim xcelapp As Excel.Application
  9.     Dim Sheet As Excel.Worksheet
  10.  
  11.     Set xcelapp = New Excel.Application
  12.     Set xcelwb = xcelapp.Workbooks.Open("QFinal4.xls")
  13.     Set Sheet = xcelapp.ActiveWorkbook.Sheets(1)
  14.  
  15.        'format the excel data
  16.        With Sheet
  17.  
  18.     Range("A1:L1").Select
  19.  
  20.     Selection.Font.Bold = True
  21.     Selection.Font.ColorIndex = 54
  22.     Selection.Interior.ColorIndex = 36
  23.     Cells.Select
  24.     End With
  25.     With Selection.Font
  26.         .Name = "Tahoma"
  27.         .Strikethrough = False
  28.         .Superscript = False
  29.         .Subscript = False
  30.         .OutlineFont = False
  31.         .Shadow = False
  32.     End With
  33.     Cells.EntireColumn.AutoFit
  34.     Range("B2").Select
  35.     Selection.NumberFormat = "m/d/yyyy"
  36.     With Selection
  37.         .HorizontalAlignment = xlLeft
  38.         .VerticalAlignment = xlBottom
  39.         .WrapText = False
  40.         .Orientation = 0
  41.         .AddIndent = False
  42.         .IndentLevel = 0
  43.         .ShrinkToFit = False
  44.         .ReadingOrder = xlContext
  45.         .MergeCells = False
  46.     Rows("1:1").Select
  47.     Selection.Insert Shift:=xlDown
  48.     Selection.Insert Shift:=xlDown
  49.     Rows("1:2").Select
  50.     Selection.Insert Shift:=xlDown
  51.     Range("H1").Select
  52.     ActiveCell.FormulaR1C1 = "CD"
  53.     Range("H2").Select
  54.     ActiveCell.FormulaR1C1 = "Policy Summary By Owner"
  55.     Range("H1:H2").Select
  56.     Selection.Font.ColorIndex = 0
  57.     With Selection.Interior
  58.         .ColorIndex = 35
  59.         .Pattern = xlSolid
  60.     End With
  61.     Selection.Font.Bold = True
  62.     Columns("H:H").EntireColumn.AutoFit
  63.     Columns("H:H").ColumnWidth = 15
  64.     Columns("G:G").EntireColumn.AutoFit
  65.     Columns("H:H").EntireColumn.AutoFit
  66.     Range("I8").Select
  67.     Columns("H:H").ColumnWidth = 18.43
  68.     Range("H1:I2").Select
  69.     Selection.Interior.ColorIndex = 35
  70.     Range("H19").Select
  71.     ActiveWindow.ScrollColumn = 2
  72.     ActiveWindow.ScrollColumn = 3
  73.     ActiveWindow.ScrollColumn = 4
  74.     ActiveWindow.ScrollColumn = 3
  75.     ActiveWindow.ScrollColumn = 2
  76.     ActiveWindow.ScrollColumn = 1
  77.     Range("H1:I2").Select
  78.     Selection.Cut
  79.     Range("F1").Select
  80.     ActiveSheet.Paste
  81.     Columns("H:H").EntireColumn.AutoFit
  82.     Columns("G:G").ColumnWidth = 15.86
  83.     Columns("G:G").ColumnWidth = 17.57
  84.     Columns("F:F").ColumnWidth = 10.43
  85.     Columns("F:F").ColumnWidth = 11.86
  86.     Columns("G:G").ColumnWidth = 14.71
  87.  Range("J4").Select
  88.     ActiveCell.FormulaR1C1 = "Surrender Value"
  89.     Range("J4").Select
  90.     Selection.Font.Bold = True
  91.     Range("J4:J5").Select
  92.     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  93.     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  94.     With Selection.Borders(xlEdgeLeft)
  95.         .LineStyle = xlContinuous
  96.         .Weight = xlThin
  97.         .ColorIndex = xlAutomatic
  98.     End With
  99.     With Selection.Borders(xlEdgeTop)
  100.         .LineStyle = xlContinuous
  101.         .Weight = xlThin
  102.         .ColorIndex = xlAutomatic
  103.     End With
  104.     With Selection.Borders(xlEdgeBottom)
  105.         .LineStyle = xlContinuous
  106.         .Weight = xlThin
  107.         .ColorIndex = xlAutomatic
  108.     End With
  109.     With Selection.Borders(xlEdgeRight)
  110.         .LineStyle = xlContinuous
  111.         .Weight = xlThin
  112.         .ColorIndex = xlAutomatic
  113.     End With
  114.  
  115.     With Selection.Borders(xlInsideHorizontal)
  116.         .LineStyle = xlContinuous
  117.         .Weight = xlThin
  118.         .ColorIndex = xlAutomatic
  119.     End With
  120.     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  121.     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  122.     With Selection.Borders(xlEdgeLeft)
  123.         .LineStyle = xlContinuous
  124.         .Weight = xlThin
  125.         .ColorIndex = xlAutomatic
  126.     End With
  127.     With Selection.Borders(xlEdgeTop)
  128.         .LineStyle = xlContinuous
  129.         .Weight = xlThin
  130.         .ColorIndex = xlAutomatic
  131.     End With
  132.     With Selection.Borders(xlEdgeBottom)
  133.         .LineStyle = xlContinuous
  134.         .Weight = xlThin
  135.         .ColorIndex = xlAutomatic
  136.     End With
  137.     With Selection.Borders(xlEdgeRight)
  138.         .LineStyle = xlContinuous
  139.         .Weight = xlThin
  140.         .ColorIndex = xlAutomatic
  141.     End With
  142.     With Selection.Borders(xlInsideHorizontal)
  143.         .LineStyle = xlContinuous
  144.         .Weight = xlThin
  145.         .ColorIndex = xlAutomatic
  146.     End With
  147.     Range("J4:J5").Select
  148.     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  149.     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  150.     With Selection.Borders(xlEdgeLeft)
  151.         .LineStyle = xlContinuous
  152.         .Weight = xlMedium
  153.         .ColorIndex = xlAutomatic
  154.     End With
  155.     With Selection.Borders(xlEdgeTop)
  156.         .LineStyle = xlContinuous
  157.         .Weight = xlMedium
  158.         .ColorIndex = xlAutomatic
  159.     End With
  160.     With Selection.Borders(xlEdgeBottom)
  161.         .LineStyle = xlContinuous
  162.         .Weight = xlMedium
  163.         .ColorIndex = xlAutomatic
  164.     End With
  165.     With Selection.Borders(xlEdgeRight)
  166.         .LineStyle = xlContinuous
  167.         .Weight = xlMedium
  168.         .ColorIndex = xlAutomatic
  169.     End With
  170.     With Selection.Borders(xlInsideHorizontal)
  171.         .LineStyle = xlContinuous
  172.         .Weight = xlThin
  173.         .ColorIndex = xlAutomatic
  174.     End With
  175.     Range("J5").Select
  176.     ActiveCell.FormulaR1C1 = " Date"
  177.     Range("J7").Select
  178.     Columns("J:J").EntireColumn.AutoFit
  179.     Range("A5:L5").Select
  180.     Range("L5").Activate
  181.     Selection.Font.Underline = xlUnderlineStyleSingle
  182.     Range("K23").Select
  183.     ActiveWindow.ScrollColumn = 6
  184.     ActiveWindow.ScrollColumn = 5
  185.     ActiveWindow.ScrollColumn = 4
  186.     ActiveWindow.ScrollColumn = 3
  187.     ActiveWindow.ScrollColumn = 2
  188.     ActiveWindow.ScrollColumn = 1
  189.     ActiveWindow.ScrollColumn = 2
  190.     ActiveWindow.ScrollColumn = 3
  191.     ActiveWindow.ScrollColumn = 4
  192.     Range("J4").Select
  193.     With Selection.Interior
  194.         .ColorIndex = 6
  195.         .Pattern = xlSolid
  196.     End With
  197.     Selection.Interior.ColorIndex = 36
  198.     Range("J4").Select
  199.     Selection.Font.ColorIndex = 13
  200.     Range("J5").Select
  201.     Selection.Copy
  202.     Range("J4").Select
  203.  
  204.     Range("C4:L13").Select
  205.     Selection.Cut
  206.     Range("A9").Select
  207.     ActiveSheet.Paste
  208.     Cells.Select
  209.     Cells.EntireColumn.AutoFit
  210.     Range("F1:F2").Select
  211.     Columns("F:F").ColumnWidth = 38.43
  212.     Range("G3").Select
  213.     Selection.Copy
  214.     Range("G1").Select
  215.  
  216.     Selection.Copy
  217.     Range("G2").Select
  218.  
  219.     Columns("F:F").EntireColumn.AutoFit
  220.     Range("B5").Select
  221.     Selection.Cut
  222.  
  223.     Selection.ClearContents
  224.     Range("B4").Select
  225.     Selection.Copy
  226.     Range("B5").Select
  227.  
  228.     Range("B6").Select
  229.     Selection.Cut
  230.     Range("A7").Select
  231.     ActiveSheet.Paste
  232.     Range("A5").Select
  233.     ActiveCell.FormulaR1C1 = "Insured Name/Date Of Birth"
  234.     Columns("B:B").Select
  235.     Columns("A:A").EntireColumn.AutoFit
  236.     Range("A18").Select
  237.  
  238. Range("G1").Select
  239.     Selection.Interior.ColorIndex = 2
  240.     Range("G2").Select
  241.     Selection.Interior.ColorIndex = 2
  242.     Range("B5").Select
  243.     Selection.Interior.ColorIndex = 2
  244.     Range("H9").Select
  245.     Selection.Font.ColorIndex = 53
  246.     Selection.Font.Underline = xlUnderlineStyleSingle
  247.     End With
  248.  
  249.     Cells.Select
  250.     Cells.EntireRow.AutoFit
  251.     ActiveWorkbook.Save
  252.  
  253. End Sub
Thanks in advance
May 16 '08 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,476
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.
May 16 '08 #2

NeoPa
Expert Mod 15k+
P: 31,476
If you mean line #18 (it's so much easier working with code in tags) then I suspect you need the . at the start.
Expand|Select|Wrap|Line Numbers
  1. .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).
May 16 '08 #3

Expert Mod 2.5K+
P: 2,545
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
May 17 '08 #4

NeoPa
Expert Mod 15k+
P: 31,476
Nice answer Stewart :) Sounds like you have a thorough understanding of this.
May 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.