Fully Qualifying - ActiveCell.SpecialCells(xlLastCell)).Select

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
doesn’t 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.

I’ve also found some Microsoft advice on this issue. It involves fully
qualifying the range object. However, I don’t 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.

Activecell exists only on the active sheet. So if oSheet is not the active
sheet then your code will bomb. Also you can not select a rang on any sheet
that is not active. You would need to select the sheet first and then select
the range. Additionally SpecialCells(xlLastCell) is kinda dangerous code to
use. The last cell is not always what you might think it is. If I understand
what you are trying to do you want to apply formatting to the entire data
range of a sheet? Do you know how many columns wide the spreadsheet is or do
you need to determine that at run time? Is there one column that will alway
be populated all the way down to the last data row? If you know both of these
things then you can use code taht looks upwards from the bottom of the sheet
to the first row with data to define the last row. Knowing that and how may
columns wide the sheet is you can accurately define the data range.
--
HTH...

Jim Thomlinson


"tmort" wrote:

> 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
> doesn’t 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.
>
> I’ve also found some Microsoft advice on this issue. It involves fully
> qualifying the range object. However, I don’t 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.
>
>

My sheet will always be 19 colums wide (A:S) and column 3 (C) would always
have an entry.

I was looking for code to do as you describe when I happened upon the
SpecialCells(xlLastCell) method.

Can you point me to an example of how to find the range by finding the last
cell of column 3?

Thanks

"Jim Thomlinson" wrote:

> Activecell exists only on the active sheet. So if oSheet is not the active
> sheet then your code will bomb. Also you can not select a rang on any sheet
> that is not active. You would need to select the sheet first and then select
> the range. Additionally SpecialCells(xlLastCell) is kinda dangerous code to
> use. The last cell is not always what you might think it is. If I understand
> what you are trying to do you want to apply formatting to the entire data
> range of a sheet? Do you know how many columns wide the spreadsheet is or do
> you need to determine that at run time? Is there one column that will alway
> be populated all the way down to the last data row? If you know both of these
> things then you can use code taht looks upwards from the bottom of the sheet
> to the first row with data to define the last row. Knowing that and how may
> columns wide the sheet is you can accurately define the data range.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "tmort" wrote:
>
> > 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
> > doesn’t 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.
> >
> > I’ve also found some Microsoft advice on this issue. It involves fully
> > qualifying the range object. However, I don’t 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.
> >
> >

Sorry for taking so long... Meetings... The range you are going to want to
work with can be defined using this code...

dim rngToFormat as Range

with oSheet
set rngToformat = .range(.range("S1"), _
.cells(rows.count, "C").end(xlUp).offset(0, -3))
end with

with rngToFormat
.border = xlThin
.interior.colorindex = 5
end with
--
HTH...

Jim Thomlinson


"tmort" wrote:

> My sheet will always be 19 colums wide (A:S) and column 3 (C) would always
> have an entry.
>
> I was looking for code to do as you describe when I happened upon the
> SpecialCells(xlLastCell) method.
>
> Can you point me to an example of how to find the range by finding the last
> cell of column 3?
>
> Thanks
>
> "Jim Thomlinson" wrote:
>
> > Activecell exists only on the active sheet. So if oSheet is not the active
> > sheet then your code will bomb. Also you can not select a rang on any sheet
> > that is not active. You would need to select the sheet first and then select
> > the range. Additionally SpecialCells(xlLastCell) is kinda dangerous code to
> > use. The last cell is not always what you might think it is. If I understand
> > what you are trying to do you want to apply formatting to the entire data
> > range of a sheet? Do you know how many columns wide the spreadsheet is or do
> > you need to determine that at run time? Is there one column that will alway
> > be populated all the way down to the last data row? If you know both of these
> > things then you can use code taht looks upwards from the bottom of the sheet
> > to the first row with data to define the last row. Knowing that and how may
> > columns wide the sheet is you can accurately define the data range.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "tmort" wrote:
> >
> > > 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
> > > doesn’t 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.
> > >
> > > I’ve also found some Microsoft advice on this issue. It involves fully
> > > qualifying the range object. However, I don’t 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.
> > >
> > >