Manipulating Excel from Access

[Andrew Gabb]

I want be able to manipulate an Excel file from Access, to add a few
extra rows and insert header info (title etc.) and to make the
header row bold.

What's the easiest way to do this? Any pointers, references? (I
don't even know where to look in the online help!)

Assume Access and Excel XP.

Andrew

 

[Douglas J. Steele]

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

 

[Andrew Gabb]

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

[etc. - good stuff snipped.]

Thanks, Doug. That's got me going along fine.

But where do you find out about the objects you're working with? Do
you just look at Excel and guess, or is there a reference for the
automation interface? (I know virtually nothing about Excel VBA or
object structure, but I'm very comfortable with Access and Word, for
example.)

Andrew

 

[Douglas J Steele]

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

[etc. - good stuff snipped.]

Thanks, Doug. That's got me going along fine.

But where do you find out about the objects you're working with? Do
you just look at Excel and guess, or is there a reference for the
automation interface? (I know virtually nothing about Excel VBA or
object structure, but I'm very comfortable with Access and Word, for
example.)

It may depend on what version of Excel, but I've found that the Excel help
file is really good in this regard. Just make sure you're in the VB Editor
when you go to the Help file.