Complex form export excel

[matt beamish]

Hello. I have (for me) a complex form containing 11 subforms, 26 visible unbound controls, 4 bound controls.

I want users to be able to export the form into excel with a few clicks. How do I achieve this? In doing a little searching it looks like Im going to have to use VBA to map controls to excel cells, but I have not done this before and my VBA is novice level.

The inbuilt exports cannot cope with the complexity, and printing to pdf also has problems with the order of controls.

(Due to issues with null values I maybe adding some further controls although these will probably be invisible).

Have attached screenshots of form in design and form view, with some blanking out, to give an idea. Hope someone can help. Thanks
Matt

 

[DCrake]

Why have you got so many subforms? it seams to me you are trying to replicate an Excel worksheet in Access. I asume that the subforms are monthly activity, if so, why not use a crosstab query instead.

 

[matt beamish]

The form is based on a parameter query that uses values from a preceding date choosing form. I needed to combine data from different queries running off the same parameter query and using forms rather rather than cross tab gives me more control.

As I needed to use unbound controls the form seemed the way to go - and also, I dont use cross tabs much....

Thanks

Matt

 

[DCrake]

It may well be that you will have to delve into VBA to get the data into specific cells in in an Excel worksheet. There is an MS article in the sample databases section on how to do this.