467,143 Members | 1,223 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,143 developers. It's quick & easy.

Formatting Excel sheet from access - Object variable or with block not set

I'm going mad!

I'm exporting a query to excel, and afterwards calculating a field, and a
sum of all fields in Excel from a sub procedure in a Module in Access.

First I used

xlSheet.Range("L" & nNoRows + 2).Select
'ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

which works fine the first time I run the sub procedure - but the second
time it doesn't work :-((

It says Activecell Object variable or with block not set !
Well I changed it to:
xlSheet.Cells(nNoRows + 2, 12).Value = "=SUM(R2C:R[-1]C)" which work every
time - but now I have another problem, which I think is the same as the one
mentioned above.

I want to put one line over the sum, and two line under - and I use the
following code:

xlSheet.Range("C" & nNoRows + 2 & ":L" & nNoRows + 2).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone

Again it works the first time I run the sub procedure - but only once

Help is appreciated after many attemps :-(

TIA jj
May 21 '07 #1
  • viewed: 1595

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by jillandgordon | last post: by
14 posts views Thread by pmud | last post: by
2 posts views Thread by LittlePhil via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.