You can use Automation to instantiate Excel from within Access and run code
to do what you're looking for.
Excel has the ability to record macros, so go into Excel, turn on the
recorder and manually do all of the formatting you need. Once you've turned
off the recorder, go in and look at the code Excel generated. Now, I haven't
recorded a macro to do everything you're trying to do, but lets assume all I
wanted 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. Excel would have
recorded 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 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, I find it confusing to create a selection and then refer to that
selection, the way Excel does.
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.) If you're using Late Binding to automate Excel
(usually a good idea, unless you can be positive what version of Excel your
users have), Access has no idea what the value of that constant is, so it's
necessary to provide the actual value. You can do this by going into the VB
Editor in Excel, going to the Immediate window, typing ?xlToRight and
hitting enter. The value of the constant (-4161 in this case) will be
returned.
In the end, the following code will perform the desired formatting.
Sub ReadFromWorkbook()
Dim objXL As Object
Dim strWkbkName As String
strWkbkName = CurrentDb().Name
strWkbkName = Left$(strWkbkName, _
Len(strWkbkName) - Len(Dir$(strWkbkName))) & _
"SampleWorkbook.xls"
If Len(Dir(strWkbkName)) = 0 Then
MsgBox strWkbkName & " not found."
Else
CreateObject("Excel.Application")
objXL.Application.Workbooks.Open strWkbkName
With objXL.Application _
.Workbooks("SampleWorkbook.xls") _
.Worksheets("Sample Data")
.Cells(2, 1).CopyFromRecordset rsCurr
.Rows("1:1").Font.Bold = True
.Range(.Columns(1), .Columns(1).End(-4161)) _
.Columns.Autofit
End With
End If
objXL.Application.Workbooks( _
"SampleWorkbook.xls").Close _
SaveChanges:=True
objXL.Application.Quit
Set objXL = Nothing
End Sub
Hopefully the above will help. My July, 2005 "Access Answer" column for
Pinnacle Publication's "Smart Access" dealt with automating Excel.
Unfortunately, my contract with Pinnacle doesn't allow to republish my
columns until 6 months after they come out, so it won't be available for
free download (including a sample database) at
http://www.accessmvp.com/DJSteele/SmartAccess.html until around January 1st.
