SmartList Export - Excel cannot open the file...

(0) Share

Report

Posted on by Jim Lines

Hello,

I've got a user in HR that runs a custom SmartList that was created in SLB which returns all employee information. When she initially runs the SL and then attempts to export to Excel, she gets the following error message (note: this is at the time SL exports the data, not when saving or opening the file):

"Excel cannot open the file [filename] because the file format or file extension is not valid."

We're on GP 2013 SP2 in a Terminal Server environment running Excel 2007. In this case, the export is creating a file with a .xlsx extension.

Of course the user restarted her computer several times (not realizing this is irrelevant b/c of the TS environment), and the export finally worked. This is only happening on this one SL and no others.

From what I've found, this error can be related to some Malware, but I do not believe this is the case and no one else has reported issue.

Has anyone else encountered this problem? If so, did you find a solution?

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (23)

Answers (1)

Derek Albaugh on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

Hello Jim,

We've seen this type of error caused by having special characters in the data being returned by the SmartList and then trying to export it to Excel.

Often, it isn't common special characters such as *, !, #, %, etc., but one example is using Integration Manager and it converts a character to a wing-ding symbol and Excel doesn't recognize it on the export.

Unfortunately, it often takes redundant troubleshooting to find the 'bad' data, such as narrowing down the data to where the issue exists. For example if you are able to return 450 records and then when trying 500 it fails, then you know the issue is between records 450 and 500.

Thanks,

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

Hi Jim,

Does the same thing happen with all users logging into the TS? Are you running the TotalFileGuard application on the terminal server? Does the workstation doing the Export have Office 2007 installed?

If you rename the file in the C:\Users\%username%\AppData\Roaming\Microsoft\Excel\XLSTART folder, does it behave the same way?

Just throwing some random ideas out there.

Kind regards,

Leslie

Was this reply helpful? Yes No
Jim Lines 4 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

Derek,

Thank you for your reply. The next time it occurs, I'll test the data myself and see what I can find. I'll follow up with my results.

Jim

Was this reply helpful? Yes No
Jim Lines 4 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

Leslie,

Thank you (as always) for your reply. I'll have our support engineers take a look at your suggestions. I will follow up.

Thanks!

Jim

Was this reply helpful? Yes No
weinmagaul 5 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

If it happens completely randomly, does it happen more often to the same group of people? Perhaps an update malfunctioned?
Reinstall Office? Try XLSX Open File Tool for recovery your documents (http://www.xlsx.openfiletool.com/)
There could be so many reasons this could happen, but all the above is where I'd start.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

Jim,

Office 2007 is no longer supported for Dynamics GP 2013 - that doesn't mean it doesn't work. I seem to remember a recent post very similar to this one. It turned out the user needed to set the association of an XLXS file and Excel on their machine. To do this, simply find an XLSX file, open it with Excel, and choose the Always open with this application option.

Was this reply helpful? Yes No
Suggested answer

Jim Lines 4 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

I believe I found the resolution to this issue. The person reporting this error most frequently was running an employee list from the '*' bookmark in SmartList. My impression is that Excel has an issue with the asterisk during the export process. I was able to replicate the issue a few times using the * favorite and then I created a new SL called "AAA" with the same parameters (none) and exported. I did not receive any errors after that. I'm hoping this is the final resolution. I'll post back if this was just a fluke.

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

Thank you Jim for the information.

Was this reply helpful? Yes No
Suggested answer

DavidMO 1,030 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

I also ran across this today in GP 2013 R2 and SmartList Builder. The SmartList was run against a SQL view with RM00101, RM00102, and SY01200 (for INET1 email addresses). It seems it was the nulls causing the issue.

Randomly when running the SmartList, the Email address column was returning non-printable characters. Run it one time and CUSTNMBR 123 (with no matching record in SY01200, thus NULL) would return something like ñUßàA. The next time it would be empty, but CUSTNMBR 234 (another NULL) would return something similar. It displayed fine in the SmartList, but gave the error when attempting to Export to Excel

I fixed the SQL View, replacing

rtrim(SY01200.INET1) AS INET1 with COALESCE(rtrim(rtrim(SY01200.INET1)), '') AS INET1

and the issue went away.

Was this reply helpful? Yes No
Jim Lines 4 on at

Like (0)

Report

RE: SmartList Export - Excel cannot open the file...

David - thanks for sharing. Though in my post from 11/1 I thought I had found my issue, but that was not the case. I still hear from users periodically when this happens. Next time I run into it I'll dig into the code and see if I can apply your logic.

Have a great day.

Jim

Was this reply helpful? Yes No