Ordering of Text in Reports When Analyzing With Excel 1

[khwaja]

When I send the report to Excel (using Analyse with Excel icon), columns in Excel are not in the same order as the report has been set out. Is there any way, this can be adjusted in Access Report? In forms, using Tab Order, it is quite easy to get around this problem, but not so in Reports.

Cheers

AK

 

[MichaelRed]

I'm sure I have not ever sent a report to Excel for "analysis", but if I WERE to feel the need to do so, I'd probably send the report's RecordSource, not the report itself. The "order" of the columns shoula - woulda thena be those of the RECORDSOURCE, which could easily be ordered by you in most cases w/o impact to the report at all. This DOES remove any calculations done in the report, however these colud be added to the report recordsource and removed from the report itself, again, not generally causing any real problems.

On the other hand, I don't use Excel to analyse database items, as nost any 'analysis' which can be done in excel can just as easily be done in Ms. A, anyway. After all, the analyze functions in excel are just a 'consolidated' reference to POEF's (Plain Old Excel Function's, which -in general- can be used directly from within Ms. A. just by including the Excel Library in your references.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[khwaja]

Thanks Michael. I think the 'Analyse with Excel" which appears when your are previewing a report is bit of a misnomer. When you hit this button, you basically transfer report contents 'as is' to Excel. What you do in Excel is a different thing. But there is no denying of the fact that being able to drop a report in Excel has a lot of merit for performing ad hoc analysis when you don't have to build queries or cross tabs. So I will beg to differ on your this point. I myself have moved away from Excel extensively but still end up doing few things (eg pivot tabling is very mundane in Access 97). I think it is a good idea to share your reports with people who do not have Access or do not want to work in Access by using this option. This works particularly well when I give users access only to the switchboard and then they can use one of the options to email temselves or simply extract a dump of data in Excel without wandering into in my DB which is obviously not a feasible option if data is coming from a table or query.

Your second point is interesting. When you say that record source should be sent to Excel, are you meaning to say that the contents of underlying query or table should be used. Or is there any way I can get around the problem I have identified. As this relates to a system users will be accessing via a menu, I would like to find some way to re order the report columns.

Cheers

AK

 

[MichaelRed]

In much the same manner as sending a "report" or other object to Excel (or another application), you can use the 'transferspreadsheet' method of Ms. A., and yes, I do mean to send the query (or "table" if you must) as opposed to the actual report. As originally stated, this does NOT send calculations done within the report itself. I would think this would actually be easier, as report formatting, group headers and footers and other pecularities do not transfer well anyway (at least from the hearsay evidence). I would geuss that you would do a few lines of programming for each report / recordset to be transfered, and simply provide a cmdButton for the actual transfer action. I generally do not use (or 'appreciate&quot MS's "switchboard", and tend to simply provide a combo box to do the selection (wheather for a for or report -or in your case- the recordset) and the cmdButton for the action trigger, although you could also use the combo box events -as long as no bad selections were made.

While I'll not delve into the merits of particular functions within Excel vs Similar ones in Ms. A., I only meant to point out that almost all of the Excel Functions are available directly from within Ms. A., simply by adding a REFERENCE to the Excel Library to your Ms. A. application. See Tools-->References on ye olde menue barree. Add the reference to your ver of Excel. most functions are rather easily implemented, some are a bit tricky, and a FEW are just not there.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[khwaja]

Thanks Michael. As usual, a very informative account from you...

I will work around transfer sreadsheetv action.

Thanks once again. Cheers

AK