I am trying to format a pivot table after it is refreshed. I have the
following code which causes an error on the first Selection.Borders line
where I clear the grid.

Range("A5:AO600").Select ' Set borders

Selection.Interior.ColorIndex = xlNone ' Remove fill

Selection.Borders(xlDiagonalDown).LineStyle = xlNone ' Clear the grid
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

The error is Runtime Error 1004. Unable to set the LineStyle property of
the Border class.

I am running Access 2007 but tried it on Access 2003 with the same result.
While I have been working on the file, I am sure I have not touched this
code, and it did work in the past. Any suggestions?

If you are automating Excel from Access I'm surprised your code even gets
that far, would need to qualify all your objects back to your reference to
Excel.

However running the code snippet as is in Excel might fail if the sheet is
protected.

In passing, it's rarely necessary to use Select, eg

Dim rng as Range '
' Dim rng as Excel.Range ' in Access with early binding
' Dim rng as Object ' in Access with late binding

Set rng = Activesheet.Range(("A5:AO600") ' in Access prefix with xlApp.etc
rng..Interior.ColorIndex = xlNone ' etc

Regards,
Peter T

"NevilleT" wrote in message
...
I am trying to format a pivot table after it is refreshed. I have the
following code which causes an error on the first Selection.Borders line
where I clear the grid.

Range("A5:AO600").Select ' Set borders

Selection.Interior.ColorIndex = xlNone ' Remove fill

Selection.Borders(xlDiagonalDown).LineStyle = xlNone ' Clear the
grid
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

The error is Runtime Error 1004. Unable to set the LineStyle property of
the Border class.

I am running Access 2007 but tried it on Access 2003 with the same result.
While I have been working on the file, I am sure I have not touched this
code, and it did work in the past. Any suggestions?

Actually running the code within Excel. Not using Access.

"Peter T" wrote:

If you are automating Excel from Access I'm surprised your code even gets
that far, would need to qualify all your objects back to your reference to
Excel.

However running the code snippet as is in Excel might fail if the sheet is
protected.

In passing, it's rarely necessary to use Select, eg

Dim rng as Range '
' Dim rng as Excel.Range ' in Access with early binding
' Dim rng as Object ' in Access with late binding

Set rng = Activesheet.Range(("A5:AO600") ' in Access prefix with xlApp.etc
rng..Interior.ColorIndex = xlNone ' etc

Regards,
Peter T

"NevilleT" wrote in message
...
I am trying to format a pivot table after it is refreshed. I have the
following code which causes an error on the first Selection.Borders line
where I clear the grid.

Range("A5:AO600").Select ' Set borders

Selection.Interior.ColorIndex = xlNone ' Remove fill

Selection.Borders(xlDiagonalDown).LineStyle = xlNone ' Clear the
grid
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

The error is Runtime Error 1004. Unable to set the LineStyle property of
the Border class.

I am running Access 2007 but tried it on Access 2003 with the same result.
While I have been working on the file, I am sure I have not touched this
code, and it did work in the past. Any suggestions?