Exporting data from multuple sub reports to excel

[Guest]

I am trying to find a good way to export data to anexcel sheet from multiple
subreports. Basically I am trying to take the data on an Access report and
also show it in Excel. The Access report is based on at least 10 subreports.
The "analyze with Excel "function does not work for this purpose. Is there a
way to to program Access to place the data from each subreport on a
spreadsheet in a particular order based on 2 criteria. I also have to format
the spread sheet with subtotals and so forth.
I have also tried saving the Access report as html and performing a web
query, but the data is misalligned.

 

[Guest]

The only way to do what you are asking is by using Automation. You will have
to be proficient with VBA and understand the Excel object model. Once you
have that mastered, it is not that difficult.

 

[Guest]

I am somewhat proficient in VBA, however do you know of any links to the
information about the Excel object model

 

[Guest]

Actually, the easiest thing to use is the object browser in tne VBA editor.
Between that and Help, you can get about all you need. A book on Excel macro
programming is not a bad idea, either.

One trick you can use is to open an Excel spreadsheet. Start a macro
recording to do what you want. Then copy/paste the macro into your VBA code.
You will have to make some minor modifications to make it work for Access,
but it does take some of the tedium out of the process.

Also, read up on the GetObject and CreateObject methods in VBA Help. They
are very important in this process. It is always better to use late binding
rather than early binding when using automation with Excel. The difference
is in how you Dim your Excel Application object.

Now, the one thing that will bite you is how you manage your objects. You
must be careful to ensure that all object references are fully qualified. If
Access encounters a reference to an Excel object it can't resolve, it will
create another instance of Excel on it's own. This instance may not get
destroyed when you are closing the objects and setting them to nothing.
Should you experience opening Excel (not in Access, but in Windows) and it
freezes, that means there is still an Excel process in memory. It will be in
Task Manager on the Processes tab, but not on the Applications Tab. This can
also occur if you don't properly close your Excel objects and set them to
nothing. With this in mind, you also have to be sure that any error handling
routines include closing the objects.

Good Luck.