Export part of form data to Excel 1

[bikerted]

I'm trying to export only the detail section of my form to Excel.

On the form I have combos filtering certain fields and a button outputting this to Excel. This works fine apart from (unlike outputting from a query) the following happen:

1) The footer section totals appear in a column with their data repeated against each record and any other controls in that section also transfer.

2) The column widths don't widen to accomodate the column headers.

3) The font colour I used transferred to Excel - black would be preferable.

Fo the last two, I know there are simple solutions in Excel, but can all three be "set" so that Excel presents the data as required? Having the grand totals is fine but only in one place!

Ted.

 

[bikerted]

I'm trying to export only the detail section of my form to Excel.

On the form I have combos filtering certain fields and a button outputting this to Excel. This works fine apart from (unlike outputting from a query) the following happen:

1) The footer section totals appear in a column with their data repeated against each record and any other unwanted controls in that section also transfer - I have tried changing Display When property, but that is only meant for printing forms and not outputting form data.

2) The column widths don't widen to accomodate the column headers.

3) The font colour I used transferred to Excel - black would be preferable.

For the last two, I know there are simple solutions by deleting in Excel, but can all three be "set" so that Excel presents the data as required? Having the grand totals is fine but only in one place!

Thank you,

Ted.

 

[grantwilliams]

Not sure about your second and third points here, but certainly for your first point, could you set up a query which only picks up the data from the detail section, then output this to Excel?

 

[bikerted]

grantwilliams,

Thanks for your reply. My difficulty with doing as you suggest is that my continuous form has a combo which is coded to filter records, so I'm outputting the form results and not those of an underlying query on which the form is based. The reason I'm not doing the latter is because - try as I might (and I just tried unsuccessfully for the nth time!) I can't get my combo to filter records in the underlying query of my form and reflect the changes on the form - the bloomin' thing merely opens the query in the background! This I could output, but then the form is naff!

Any suggestions would be most greatfully received.

Ted.

 

[grantwilliams]

How are you using the combo boxes to filter the query? Are you using VBA to modify the recordource? I've done this previously using the AfterUpdate event and two functions named SetCriteria and AddToWhere. Basically, the AfterUpdate calls the SetCriteria function, which in turn calls the AddToWhere function for each specified control.

Eventually what I end up with is two variables: SelectSQL and WhereSQL, where SelectSQL is the the fields/tables that I want to select from and WhereSQL contains the Where criteria.

You could then use DoCmd.OutputTo acOutputQuery, SelectSQL & WhereSQL &";", acFormatXLS

I'm probably not telling you anything that you don't already know here, but if you want some code for this, I'm happy to pass on.

Grant

 

[bikerted]

Grant,

You're another one of Tek-Tips diamonds.

I'm afraid I'm a novice with VB, and I'll probably stretch your a patience somewhat, but any assistance with codes will be greatly appreciated. On another form - this time not a modal pop-up - I have about 14 combos "filtering" criteria on the underlying query with combo referral code against each criteria with OR Is Null against each, which leads to some rather unwieldy SQL! Your method - when I fully grasp it - may be a better way there to.

Thanks for your efforts.

Ted

 

[bikerted]

Grant,

I appear to have resolved this - based on how thing swere done on the other form I mentioned. I used Me.Requery in the combo and it now filters and the query result can be output to Excel. The only thing (which is slightly unsatisfactory) is my means of showing all records - I close and open the form with the Application.Echo False and True. It works but still flashes a bit!

Thanks for taking the time.

Ted