Excel formatting

[Tom]

I haven't done this before, so I'm not sure I can do it. I'm hoping someone can
provide some guidance, before I start trying to do something that can't be done.
I'm currently using Access 2000, but could *probably* get 2003, if necessary.
(I've been planning on getting 2003 sometime in the next fiscal year, which
starts next month.)

I want to move the contents of a table (minus a couple columns...if possible)
out to Excel. This part *seems* relatively straighforward. Although...I'm not
sure if I want to "copy", "transfer spreadsheet", or read thru the table rows
and write out one row at a time.

Then there's the formatting...

What I've been doing manually, that I'd *like* to do programmatically, if I can,
is:

1.) insert a row between rows 1 & 2.
2.) make rows 1 & 2 the rows I print on every page.
3.) set column widths.
4.) set certain columns to center its data.
5.) set row 1 for borders around each cell.
6.) set row 1 for bold printing.
7.) add gridlines.
8.) center vertically and horizontally.
9.) set left and right margins.
10.) set orientation to landscape.
11.) leave scaling at 100%.
12.) adding text to left & right header.
13.) setting font size & bold italic in header.
14.) adding text to left & right footer.
15.) setting font size & bold italic in footer.

Some of this would be uneccessary if Access could get Excel to use my default
workbook format, which from what I gather, can't be done. But I also haven't
been able to find any source where something like this is covered, where the
code examples are given, where properties that I can change are listed, and what
values are available/valid.

I'm hoping that someone could tell me if all this is possible, along with either
some code examples and/or somewhere I might find what I need. Any help you can
provide would be much appreciated.

Thanks in advance,

Tom

 

[Klatuu]

Tom,
Every thing you want to do here is possible. Be aware that coding for Excel
becomes very verbose.

Here is the basic code for creating an instance of Excel to start working
from:

Private xlApp As Excel.Application 'Application Object
Private xlBook As Excel.Workbook 'Workbook Object
Private xlSheet As Excel.Worksheet 'Worksheet Object

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
xlBook.Activate

These lines are very important, and should be in a place that even if your
error trapping takes over, they will still execute. If they do not, then an
instance of Excel will still show in the Processes tab of Task Manager and
will cause problems.

xlBook.Close
xlApp.Quit

You also have to deal with saving the Excel File. This is the line to save
it, but I do a lot of stuff before I get there. First I do an API call to
get a Common Dialog box then I do a FileSearch to be sure the file doesn't
already exist, and if it does, a message box, etc.

xlBook.SaveAs FileName:=varGetFileName

As to all the fun stuff in between, I have found the using the Object
Browser in the VB Editor and looking through the available objects,
properties, and methods, I can usually find what I need.

Have fun with it

 

[Tom]

I appreciate the help, thanks again.

Tom,
Every thing you want to do here is possible. Be aware that coding for Excel
becomes very verbose.

Here is the basic code for creating an instance of Excel to start working
from:

Private xlApp As Excel.Application 'Application Object
Private xlBook As Excel.Workbook 'Workbook Object
Private xlSheet As Excel.Worksheet 'Worksheet Object

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
xlBook.Activate

These lines are very important, and should be in a place that even if your
error trapping takes over, they will still execute. If they do not, then an
instance of Excel will still show in the Processes tab of Task Manager and
will cause problems.

xlBook.Close
xlApp.Quit

You also have to deal with saving the Excel File. This is the line to save
it, but I do a lot of stuff before I get there. First I do an API call to
get a Common Dialog box then I do a FileSearch to be sure the file doesn't
already exist, and if it does, a message box, etc.

xlBook.SaveAs FileName:=varGetFileName

As to all the fun stuff in between, I have found the using the Object
Browser in the VB Editor and looking through the available objects,
properties, and methods, I can usually find what I need.

Have fun with it