I am trying to change the formatting of an Excel worksheet from within
Access. I have found a way to approach this using setting the format
properties for a cell range. It sets the background white and prints (or
doesnt print) borders for the active area. It produces what I want except
that if I run it twice in a row I get a Method €˜Range of Object Global
Failed error.

Ive also found some Microsoft advice on this issue. It involves fully
qualifying the range object. However, I dont know how to fully qualify the
ActiveCell.SpecialCells(xlLastCell) statement.

The code to set the cells white and set border properties is below. After
that is the Microsoft example of how to resolve this issue.


oSheet.Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select

With Selection.Interior
..ColorIndex = 2
..Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
'.LineStyle = xlContinuous
..LineStyle = xlNone
'.Weight = xlThin
'.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
'.LineStyle = xlContinuous
'.Weight = xlThin
'.ColorIndex = xlAutomatic
..LineStyle = xlNone
End With
With Selection.Borders(xlEdgeBottom)
'.LineStyle = xlContinuous
'.Weight = xlThin
'.ColorIndex = xlAutomatic
..LineStyle = xlNone
End With
With Selection.Borders(xlEdgeRight)
'.LineStyle = xlContinuous
'.Weight = xlThin
'.ColorIndex = xlAutomatic
..LineStyle = xlNone
End With
With Selection.Borders(xlInsideVertical)
'.LineStyle = xlContinuous
'.Weight = xlThin
'.ColorIndex = xlAutomatic
..LineStyle = xlNone
End With
With Selection.Borders(xlInsideHorizontal)
'.LineStyle = xlContinuous
'.Weight = xlThin
'.ColorIndex = xlAutomatic
..LineStyle = xlNone
End With



Microsoft Discussion


1.
Start a new Standard EXE project in Visual Basic. Form1 is created by
default.
2.
Click References from the Project menu and check the Object Library for the
version of Excel you intend to automate.
3.
Place a CommandButton on Form1.
4.
Copy the following code to the Code Window of Form1:
Option Explicit

Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

5.
On the Run menu, click Start or press the F5 key to start the program.
6.
Click the CommandButton. No error occurs. However, a reference to Excel has
been created and has not been released.
7.
Click the CommandButton again and note that you receive one of the errors
previously described.

NOTE: The error occurs because the code refers to the Cell's method without
preceding the call with the xlSheet object variable.
8.
Stop the project and change the following line:
xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello"

to:
xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10, 2)).Value = "Hello"

9.
Run the program again. Note that you can run the code multiple times without
error.