Kevin,

I think the Export button defaults to pdf. If the users don't know they can change the Sage As file type at the bottom, they may not know there are a couple of Excel options in there.

Many people have stored backups in the *.saj folders. I believe the backup takes the folder level, not individual files, so when doing a backup of the *.saj folder into the *.saj folder... you probably get the idea. Backups and restores are negatively affected (and the current version will not allow users to do that anymore). I don't know if performance would be affected otherwise, from anything else in there because I have never tested it, but I never store anything in that folder as a rule.

Richard,

I wasn't sure if it was the wrong format or the wrong data. What I remember hearing is that it didn't give them the same information as "Open in Excel" which was the problem. I'll still ask them to try and see what happens.

Kevin DariFill said:

other information in the Company folder which added up to around 10gb. He said that could negatively impact performance, so we should clear it.

It would take up a lot of space, and probably prevent backups in the software from working at all.  So those should definitely be cleaned up.    Prior versions of Sage 50 did not prevent a circular backup into the source folder (from 2008 through 2014?), and the backup doesn't work off a list of files, it just vacuums up everything in the folder - so there's a lot of messed up <company>.SAJ data folders out there!

Sage 50 doesn't scan around that folder looking randomly at files, it just reads and writes to ibdata1 (and the two log files).   If there were so many files in a folder that the operating system time to locate a particular one was long, it could impact performance.

But from the time you see the report on screen (actually by the time you see "searching for printer", because it previews the page breaks), the Export process is working out of RAM and a temp file.  (i.e. 08/10/2015  11:05 AM        23,761,203 tmp5D78.tmp.SimplyReport)  

Since the report module can dump everything to a text file or Excel 2.1 format in 3 seconds, but going through Excel takes 30 minutes, the problem is downstream from the report module.  It defies all common sense and logic to suggest a look for a problem upstream, in the database!   It's like calling the electric company about your well pump when the toilet is plugged.

Kevin DariFill said:

it didn't give them the same information as "Open in Excel" which was the problem

It's the same data, but not properly formatted, and it will open in Excel 2013 with a security warning, then sort of die with a white screen, then after I reboot it looks like this, with the quotes showing instead of formatting cells as text:

You may want to ask them if they've tried 'XLGL', there's a 30 day demo at www.logicimtech.com.  Since it runs as an Excel add-on, the data import is directly from the database into Excel.

Hmmmm no way to delete a post....

Richard S. Ridings said:

There are other issues with 32 vs 64 bit versions in some aspects. I don't know if exporting is affected by the bit version of Excel but VBA programming is when connecting to the data.

My understanding is that Sage 50 will not directly export through / "Open with" 64 bit Excel *at all*. 

Richard S. Ridings said:

I didn't become a Certified Consultant to get rich, I was born that way!

I've also found that joining that program left me with a small fortune, but I started with a large one.

Hello all!

I just wanted to follow up to let you all know that after a few calls to Sage 50, a lovely tech support named Robert was able to locate the solution for us.

He linked me to an article ID:16639, Title: Exporting reports to Microsoft Excel 2013 is very slow

I'm not sure how I missed that before, but for us the final solution came to uncheck Use freeze panes when copying report to Microsoft Excel.

For anyone who might stumble across this thread later while googling, the resolution in that article is as follows:

Option I:

1. Open Microsoft Office Excel 2013
2. Select File, then Options
3. Left-click Advanced and scroll down under Display
4. Check Disable hardware graphics acceleration  
5. Select OK

Option II:

When copying report to Microsoft Excel under the Microsoft Excel options, uncheck Use freeze panes and under Report header option, check Show header in Microsoft Excel worksheet.

We have some very happy accountants now :)

Kevin

Kevin DariFill said:

When copying report to Microsoft Excel under the Microsoft Excel options, uncheck Use freeze panes and under Report header option, check Show header in Microsoft Excel worksheet.

Not sure where you see these options?

This is the Sage 50 Canadian Export to Excel screen:

This is the Open in Excel screen:

If you're working with the U.S. (Peachtree) software, please be aware that it's as different (codebase, menu items, workflow, database backend, etc.) from Sage 50 Canadian, as is QuickBooks.  The only options in the Sage 50 Canadian software are the type of export, and the filename.  

Problems with exporting to Excel through the Microsoft Office APIs are generic, so the problem / solution is valid.   The problem is in the Microsoft Office settings, Excel is trying to format and prettify the document instead of waiting 'till the end of the export.

I had a little trouble finding article 16639, it is in the Knowledgebase for the U.S. ("Peachtree") version of Sage 50.

I may have fixed this problem on my workstation while trying to get the graphics card to not crash *every* time I typed text into an Outlook email (which is pretty often)  It worked, now it hardly crashes more than a couple times a week, and Excel is no longer painfully slow.

The complete fix, is to google 'Turn off Animation in Microsoft Office'. 

The animations are quite helpful when learning Excel, but after a few years of practice, the 5-15 second wait to paste text is too annoying.

Exporting reports to Microsoft Excel 2013 is very slow
Products
Sage 50—U.S. Edition 2015 Sage 50—U.S. Edition 2014
Description
Exporting reports to Microsoft Excel 2013 takes 15 to 20 minutes for the same data that would export to Microsoft Excel 2010 in less than one minute
Cause
New enhancement in Microsoft Office 2013 called GPU rendering