Modifying data on export to excel

[Kozbot]

Hello. I have a query that I want to export to excel.

For some reports certain fields may be null entirely. Is there a way to remove these fields when exporting to excel or hide them automatically? I basically want to hide the fields to make the report more printable

I guess another solution to this problem would be to hide the fields in the query based on that criteria before exporting to excel. is that possible?

 

[Rx_]

If you are using an export to excel built-in function, it is a canned function.
http://www.btabdevelopment.com/ts/tq2xlspecwspath
strTQName - the name of the query
The query can be modified to meet your exact criteria, then pragmatically move the results of that query to Excel.
There are different versions of using an existing Excel Workbook or creating a Workbook.

In many cases, the Excel can be opened with the reference and Excel Object Code (VBA) can specifically set cells formatting or values. For example - set all dates within 30 days of Now() with a background color of yellow.

 

[Kozbot]

If you are using an export to excel built-in function, it is a canned function.
http://www.btabdevelopment.com/ts/tq2xlspecwspath
strTQName - the name of the query
The query can be modified to meet your exact criteria, then pragmatically move the results of that query to Excel.
There are different versions of using an existing Excel Workbook or creating a Workbook.

In many cases, the Excel can be opened with the reference and Excel Object Code (VBA) can specifically set cells formatting or values. For example - set all dates within 30 days of Now() with a background color of yellow.

Then how can I modify my query (which is a parameter query) to omit or hide the null fields without affecting fields with data in them?

 

[Rx_]

If it is a query, remove the field names from the Select statement
In design mode, un-check the box for the field.

 

[Kozbot]

If it is a query, remove the field names from the Select statement
In design mode, un-check the box for the field.

Can I do this automatically with VBA depending on if the field is empty of data? Some of data I query will have certain fields empty. I want these hidden for the export of the data to excel.

 

[Rx_]

Not so much automatically.

You could build a totals query on the dataset that returns a single record.
Return that into a DAO recordset.
Then, evaluate each field for Null.

Then, build a VBA recordset function that calls the field number based on null/not null.

Also post a question in the Query section.
Perhaps two queries, one that is a summary to determine fields with only null Union or joined with the data query.
I would have to think about that one some more.