Formatting Excel from Access

  • Thread starter Thread starter danijela.simunovic
  • Start date Start date
D

danijela.simunovic

Hi!
Is there a way to format Excel from Access? I am exporting a table with
X Columns from Access to Excel and I would like the first row to be
lets say Italic and bold and the textcolor would be red and all the
others would be yellow, the fill color would be red and the would be
font 14. The format would take place only from the first to the last
column(X) where i have some data and to the last row with data.
Just tell me how can i select a section of a sheet like A1:D1 and
format it.
Thanks!

Danijela
 
If you'd looked back a couple of days, you would have found the following
response I gave Andrew Gabb in a thread entitled "Manipulating Excel from
Access":



Easiest way is to do what it is you want one-time in Excel, recording it as
a macro.

Once you're done, look at the VBA code Excel uses, and adapt it for use from
Access.

For example, assume you want the first row (the field titles) to be bold,
and each of the columns to be expanded to be large enough to show all of the
data. Recording what's necessary in Excel to accomplish this will give the
following macro:

Rows("1:1").Select
Selection.Font.Bold = True
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Columns.AutoFit

Unfortunately, it's not quite as simple as just plugging that code into
Access.

Because Excel assumes that the VBA code is working with Excel objects, it
can take a few shortcuts with referring to the objects. When you're running
from inside of Access, you have to be explicit. It's not sufficient, for
example, to refer to Rows: you need to indicate Rows on which spreadsheet.
As well, it's not really necessary to create a selection and then refer to
that selection, the way Excel does: you can combine those two steps into
one.

Note, too, that the 3rd line of code about refers to an intrinsic Excel
constant xlToRight (if you're not that familiar with Excel VBA,
Selection.End(xlToRight) lets you extend the current selection to the right
until the last populated cell. In this case, since the selection is a
column, this means that the resultant range will be all contiguous columns
that have some data in them.) It you're using Late Binding, Access has no
idea what the value of that constant is, so it's necessary to provide the
actual value.

In the end, the following code will perform the desired formatting.

With objActiveWkb.Worksheets(intCurrSheet)
.Rows("1:1").Font.Bold = True
.Range(.Columns(1), .Columns(1).End(-4161)) _
.Columns.Autofit
End With
 
Thanks! just one question: What do i have to put instead of
"intCurrSheet" and "objActiveWkb"?
 
The code assumes that you're using Automation along the following lines:

Dim objActiveWkb As Object
Dim objXL As Object
Dim strWkbName As String

Set objXL = CreateObject("Excel.Application")
objXL.Application.Workbooks.Open "C:\Data\MyBook.xls"
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb.Worksheets(intCurrSheet)

where intCurrSheet is the number of the worksheet (1 is the first sheet, 2
is the second sheet, and so on)

(replace C:\Data\MyBook.xls with the appropriate path to your workbook)
 
Thanks! I got the hang of it! Bu still I don't know how to get the
border color or border thickness and most important fill color!? Can
you help me out?
I tried .Range("A1:D20").Fill.Color = Red .... and so on but no luck!
Thanks in advance!

Danijela
 
Looking in the Excel help file, the Fill property doesn't appear to apply to
ranges, but is part of Charting.

If you're trying to set a colour for the interior of the cells in your
range, you need something like:

With objActiveWkb.Worksheets(1)
With .Range("A1:D10").Interior
.ColorIndex = 3
.Pattern = 1 ' xlSolid
.PatternColorIndex = -4105 ' xlAutomatic
End With
End With

Note that as I said before, since my code uses late binding, I can't use the
intrinsic constants like xlSolid and xlAutomatic Excel uses. Therefore, I'm
using the numeric values of those constants, but include the name of the
constant for documentation purposes.
 
I have no words!!! Thanks!
I'll try to find how to get border style and color and font style and
color and if I get stuck i'll post to the news hoping that I'll get an
answer from you or somebody...
Thanks again because your answers were very very helpfull!!

Danijela
 
As I said, use the Macro Recorder in Excel as a starting point. At least
then you'll know the correct objects!

Good luck.
 
Back
Top