UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Hidden Fields To Excel From Report, Access 2016    
 
   
tohas
post May 24 2020, 06:30 AM
Post#1



Posts: 93
Joined: 9-July 10



Hi,

we have a huge amount of reports which are exported direct to excel from page preview.

Now the user want some more fields exported to excel but this text fields should not visible in the reports.

because I don't want to code all new with extra menus and DoCmd.OutputTo acReport, .. I tried to extend the reports with hidden fields (.notvisible)

But then the fields are not shown in XLS.

is there a trick to do this?

Thanks
This post has been edited by tohas: May 24 2020, 06:31 AM
Go to the top of the page
 
Jeff B.
post May 24 2020, 07:11 AM
Post#2


UtterAccess VIP
Posts: 10,491
Joined: 30-April 10
From: Pacific NorthWet


One approach might be to export the underlying query to Excel, rather than the report. Your query can have any number of fields that don't show up in the report.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
projecttoday
post May 24 2020, 07:22 AM
Post#3


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


I don't think you can do what you want. The closest would be to pass a parameter to the report which turns those fields on or off depending on the user's choice. But when looking at the exportable version, the user would see the unwanted fields. But at least you would not have two versions of the report, something you definitely want to avoid.

--------------------
Robert Crouser
Go to the top of the page
 
tohas
post May 24 2020, 08:04 AM
Post#4



Posts: 93
Joined: 9-July 10



@all thanks

reprogramming is not an option because there are more than 30 reports and the based queries are not static!

I don't know if it works perfect in real life, but I was setting the fields to visible and gave the ForeColor #FFFFFF and Width 0.3

Now they are exported to xls. The only point is that the white column (fortunately header is black) must be formatted from white to black by user.

Perhaps the excel team can write an autopen makro that is looking for this known columns doing this job. But this is not my problem ;-).

Go to the top of the page
 
theDBguy
post May 24 2020, 08:34 AM
Post#5


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (tohas)
is there a trick to do this?

You could try using the CopyFromRecordset method.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post May 24 2020, 08:56 AM
Post#6


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


QUOTE
must be formatted from white to black by user.

The user can format from visible to not visible. How is this different?



--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post May 24 2020, 09:06 AM
Post#7


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


I wonder if you really need 30 reports. Is there some repetition there? Maybe you could reduce the programming workload by reducing the number of reports. Or maybe not.

--------------------
Robert Crouser
Go to the top of the page
 
tohas
post May 24 2020, 12:01 PM
Post#8



Posts: 93
Joined: 9-July 10



there are 200 tables and the reports comes from 30 tables
Go to the top of the page
 
tohas
post May 24 2020, 12:06 PM
Post#9



Posts: 93
Joined: 9-July 10



I wrote: Now they are exported to xls. The only point is that the white column (fortunately header is black) must be formatted from white to black by user.

That means: Access exports to Excel. The Export is not plane text. Thats why the white formated text from the Access report (you remember some filelds are #FFFFF) appears in Excel as white.
Go to the top of the page
 
projecttoday
post May 24 2020, 12:07 PM
Post#10


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


Hmmm ....

What does this database do? Was the person who designed this database a skilled developer? Can you provide a list of these tables? And the reports? Or a partial list? Or post the database?

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post May 24 2020, 12:18 PM
Post#11


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


You want to format the spreadsheet after exporting? That would be Excel automation. Exactly how is this export performed?

--------------------
Robert Crouser
Go to the top of the page
 
tohas
post May 24 2020, 01:16 PM
Post#12



Posts: 93
Joined: 9-July 10



this db is a small database. I know some db's with more than 1200 tables ohyeah.gif

it was designed by a team of ibm developers in former times.

I can't give you the erm's.


Go to the top of the page
 
projecttoday
post May 24 2020, 03:29 PM
Post#13


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


Please respond to post #11.

So you're trying to save repetitive programming on 30 reports. You may have said so already but what exactly would you do if you only had 1 report to change?

Getting back to the design. Are these tables all really distinct from one another? Are the reports all really distinct from one another? I mean "Balance Sheet" and "Income Statement" not "Balance Sheet Department 1" and "Balance Sheet Department 2".


--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 05:45 AM