Center page header blank after exporting report to Excel

Lochkelly · ‎September 20, 2023

Yesterday our QB Desktop 2021 quit entering information in the center of page headers on reports exported to Excel.

I've updated QB and Excel 365 (which was already up-to-date). I've run Quick Fix My File, Quickbooks File Doctor, Quick Fix My Program, and Quickbooks Program Diagnostic Tool. I've restarted the PC several times. Nothing has corrected this.

I will include screenshots of a report from last month and from today to illustrate.

Lisa Kelly

BigRedConsulting · ‎October 13, 2023

With your text I was eventually able to reproduce the issue here.

The problem is that the length of the Excel worksheet header fields is limited to 255 characters total (left, middle, and right section lengths combined).

So, if QuickBooks tries to set the header where the text is longer, it fails. It may be trying to do that, or it may notice the text is too long and not even try. However, either way it doesn't tell you that the header couldn't be set.

I was able to reproduce the issue only after changing the default fonts for the various fields in the header and by picking fonts with long names. Changing the fonts broke it because the font names are actually written to the header and then Excel converts that encoding to display the text using those fonts.

Here's an example of what's actually written to the excel header using your text and some fonts I chose:

&"Bookman Old Style,Italic"&12 First Presbyterian Church of Moncks Corner
&"Lucida Sans Unicode,Italic"&14 6630 WORSHIP/EVANGELISM/FELLOWSHIP
&"Bookman Old Style,Italic"&11 January 1 through October 13, 2023

The text above, including carriage returns, is 209 characters long, which actually fits.

BUT, if the left header is also set and includes the default prep date, time, and basis fields, then the left header will look something like this internally, depending on your font:

&"Arial,Bold"&8 9:16 AM
&"Arial,Bold"&8 10/13/23
&"Arial,Bold"&8 Accrual Basis

Using the font Arial Bold it is 80 characters long.

And, 80 + 209 is... too long. And so the center header isn't set to anything - either because QuickBooks doesn't try or because it does try and it ignores the resulting 1004 error that Excel returns in this case.

I'm not sure what your fonts are for these various header fields, but if they have long names then using fonts with short names may help, and if you remove one or more of the left header fields from the report before sending it to Excel, then it should work much more reliably.

For example, when I set every field to Arial and used your text, it just barely works. When I did that, I got this result in the header fields:

&"Arial,Bold"&8 9:56 AM
&"Arial,Bold"&8 10/13/23
&"Arial,Bold"&8 Accrual Basis
&"Arial,Regular"&12 First Presbyterian Church of Moncks Corner
&"Arial,Regular"&14 6630 WORSHIP/EVANGELISM/FELLOWSHIP
&"Arial,Regular"&11 January 1 through October 13, 2023

The combined text above is 254 characters long, and so there is room for just one more character!

That single character could get used up if the time was four minutes later: 10:00 AM.

Worse, if you chose a different date format for the date, such as "October 13, 23", then it would fail as the resulting header would be way too long. Or, if the auto-generated date range text, "January 1 through October 13, 2023", was longer, then it would also fail. For example, "February 1 through February 28, 2023" is two characters longer, and so would cause the failure.

So, the best way to make this work reliably is to change your reports so that far less text is written to the header, so that you're not close to the limit of 255 characters. You can do this by removing fields from the report header, using fonts with short names, and by shortening your text.

Hope this helps!

View solution in original post

BigRedConsulting · ‎September 22, 2023

As you export, there's a setting that controls this:

If you click Advanced as you export the report to Excel, then if you have the first option selected as shown above, you won't see the title unless you print the report.

If you click the second option, then you'll see the title on the worksheet just like your 'before' example.

Is this what you're seeing?

Lochkelly · ‎October 04, 2023

I have tried that by setting it, unsetting it, and over again. No affect.

Thanks for trying.

Lisa

Lochkelly · ‎October 12, 2023

I'm still having problems with this, no matter what settings I use. I've updated Office 365 and therefore Excel, QB Desktop Premier Nonprofit 2021 is up-to-date, I've also run the Company File and Program repairs in the Tool Hub. Nothing is fixing this. (I'm on Windows 10 still.)

Can anyone help me??

Lisa

BigRedConsulting · ‎October 12, 2023

Does it work if you try the same thing in a sample company?

Kevin_C · ‎October 12, 2023

Hello there, Lisa. I'll chime in to share some additional troubleshooting steps with viewing your QuickBooks Desktop (QBDT) report's header in Excel.

Let's start by following BigRedConsulting's recommendation above to try opening your sample company to check if the issue occurs the same. Here's how:

Go to the File menu and click Close Company.
You will see the No Company Open window.
Select the drop-down arrow beside Open a sample file.
Select Sample product-based business. Then, click OK.
Select I’m not the admin and tap Continue.
Click OK.

Once you're in the sample company file, choose a report and export it to Excel and ensure to select the On printed report and screen option as suggested above.

If the header shows up in the sample company, we can review your Report Format Preferences to check if you have selected the correct header information to display in your reports.

However, if your preferences are not working as expected, we can revert it to the default format to fix it by following these steps:

Back up the QuickBooks company file.
From the Edit menu, select Preferences.
Choose Reports & Graphs and select the Company Preferences.
Click the Format button.
In the Report Format Preferences page, go to the Header/Footer tab and click Revert.
Hit OK to close the Preferences window.

But if you get the same results when using the sample company, I recommend contacting our Technical Support Team. They have the tools to look into your account securely and can do further investigation into this behavior.

Furthermore, you can check out these articles to learn more about the different reports in QuickBooks Desktop and how you can customize them:

I'm always around and ready to help if you have more questions about exporting reports in QBDT. I'll be here to lend a helping hand.

Lochkelly · ‎October 12, 2023

Yes. Yes, it does.

Lochkelly · ‎October 12, 2023

I was on chat and remote with support and they essentially decided that I needed to reinstall QuickBooks. Which I have done. Still no joy.

This is so frustrating. While not crucial in the grand scheme of all things accounting, it is still a feature I want.

I'm not spending more time with customer support. The conversation today didn't leave me with much confidence in their abilities. For instance, not fully reading what I've told them and repeating their questions to me. Ugh.

Lisa

Lochkelly · ‎October 12, 2023

Sample file acts the same way. See my comments about customer support...

Lisa

BigRedConsulting · ‎October 12, 2023

Q: Does it work if you try the same thing in a sample company?

@Lochkelly RE: Yes. Yes, it does.

OK, so if it works in the sample company but not your company, then there may be something about the text of the report header that makes it so it can't be added to the Excel header.

What is the exact text in the the three rows of the header on the report?

These rows:

Lochkelly · ‎October 12, 2023

All of the headers appear in the Customize Report window but the center header still does not appear in the Excel spreadsheet headers.

Lisa

Lochkelly · ‎October 12, 2023

Sorry, I meant to say that it does the same thing in the Sample Company. (My brain is fried by trying to figure this out!) Thanks for helping!

BigRedConsulting · ‎October 12, 2023

RE: All of the headers appear in the Customize Report window but the center header still does not appear in the Excel spreadsheet headers.

Please reply and send the exact text of those fields, using copy/paste. Without the complete text, which I think matters, I can't test it here.

Lochkelly · ‎October 12, 2023

First Presbyterian Church of Moncks Corner

Custom Transaction Detail Report

October through December 2023

Is this what you meant?

Interestingly, some of the included reports DO include center headers in the exported spreadsheet, but not all and definitely not any custom reports...

Lisa

BigRedConsulting · ‎October 12, 2023

Well, that look like some default values, but that's not what's in your previous screenshot, which looks a lot longer and more involved, but I can't see the full text in your screenshot as it's clipped off.

Lochkelly · ‎October 13, 2023

First Presbyterian Church of Moncks Corner

6630 WORSHIP/EVANGELISM/FELLOWSHIP

January 1 through October 13, 2023

I believe this is the same report as the screenshot.

Lisa

BigRedConsulting · ‎October 13, 2023