"Scott" <Sc
...@discussions.microsoft.com> wrote in message
...
> I have been programming in VB for many years and I have never had to use
any
> of the MS Office objects until recently.
> I need to create a report in Excel using VB. Our BA has given me the
format
> how the Excel report should be laid out. I will be getting my data from a
> Sybase system for these reports. As the reports change I will need to
> produce a new Excel Sheet for each report. There will also be subtotals
for
> each category on each sheet as well. No grand totals as of the latest
> requirements. This would have been much easier with Crystal but they
choose
> Excel instead.
> How do I do this?
> Thanks for your help in advance.
Several items...
First, if this is your first time, and the company will spring for it, I
suggest you get a copy of the Office Developer Toolkit.
http://msdn.microsoft.com/office/. Most of the items in the developer kit
you can either download separately or find-work-arounds. But it is nice to
have the whole packages as you get a the help files, sample code already on
the CD, etc. In any case mark the office development sites as a favorite, as
they will become your best friend. <g>
Second, you can get better help using the Excel newsgroups
"microsoft.public.excel.~". But you need to get to the point of being able
to ask specific questions. Broad "can you write this for me" type questions
are usually ignored, or answered like this one - with a broad answer. <g>
Third, run through a couple of the samples so you get a feel for the object
model, how to open an excel spreadsheet, open template, etc.
Fourth, you have several different options for doing what you want to do.
1) Write to each specific 'cell'.
2) Use tags for cells on Excel and in the vb code assign values to those
tags. (a better approach to preserve sanity)
3) Use a hidden block of cells, and update that, using Excel features to
reference these cells.
4) Use a separate data file, base the Excel template report on this
datafile, create the data file using VB, then update.
5) Create a recordset in VB and load the Excel template with the recordset,
....
....
Each method has its trade-offs. Some are simpler going in, but can lead to
problems when distributed and scaled upwards. Others are great when scaled,
but annoying to set up initially. That is why I strongly recommend playing
with the various pieces of sample code, BEFORE committing to any one method.
A google search should also produce some good examples.
hth
-ralph
