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
