how to turn on shading and boarders in Excel from Access

M

Mo

I have an application that runs several queries and then outputs them to
Excel. I am using Access 97 and have just recently changed some of the
queries to output to Excel using transferspreadsheet rather than via a macro.
This was done to get around an Excel row limitation problem. The problem
with this new way is that the Excel output no longer automatically shades the
headings and no longer has boarders. We don't want our users to have to
manually do these steps. Can anyone assist with the code to do this via
Access?

For shading the 1st row I was experimenting with the following:

XLAPP.Rows("1:1").Select
With XLAPP.Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

It selects the row ok and isn't erring but the fields are not being shaded.
help!
 
R

Randy Harris

Mo said:
I have an application that runs several queries and then outputs them to
Excel. I am using Access 97 and have just recently changed some of the
queries to output to Excel using transferspreadsheet rather than via a macro.
This was done to get around an Excel row limitation problem. The problem
with this new way is that the Excel output no longer automatically shades the
headings and no longer has boarders. We don't want our users to have to
manually do these steps. Can anyone assist with the code to do this via
Access?

For shading the 1st row I was experimenting with the following:

XLAPP.Rows("1:1").Select
With XLAPP.Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

It selects the row ok and isn't erring but the fields are not being shaded.
help!
Click to expand...

This kind of stuff has been working well for me.

Dim XL As Object
Set XL = GetObject(FileName)
XL.Application.Visible = False
XL.Parent.Windows(1).Visible = True
XL.Application.Range("AJ1:AM1").Interior.ColorIndex = 37
XL.Application.Range("AS1:AV1").Interior.ColorIndex = 39
' Format for the Column Headings
With XL.Application.Rows("1")
.HorizontalAlignment = -4108
.VerticalAlignment = -4160
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
.RowHeight = 40
.AutoFilter
End With
For iLine = 7 To 11
With XL.Application.Range("A1:AU1").Borders(iLine)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
Next
' Freeze the panes
XL.Application.Range("E2").select
XL.Application.ActiveWindow.FreezePanes = True
' Some columns need to be centered - 4108 is centered
XL.Application.Columns("C").HorizontalAlignment = -4108
XL.Application.Columns("F").HorizontalAlignment = -4108
 
M

Mo

Thanks Randy, this was very helpful. I just need to tweak it a bit. The
example you gave for borders is for a range. How would I turn on the borders
for the entire worksheet? For my specific application I can't specify a
range as it is different each time the users run the query. Thus far I've
been able to select all the cells using XLAPP.Cells.Select and I tried part
of your sample using the select vs a range but no luck yet. Thanks
 
Top