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.
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.
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.
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.
Thank you (as always) for your reply. I'll have our support engineers take a look at your suggestions. I will follow up.
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.
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.
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.
Thank you Jim for the information.
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.
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.
I am new here and experiencing the same problem. MS has advised that it is a "Characer/Data" issue.
My Export is from GP for an import from a in-house system which takes plave monthly. I can run the SL report for first half of the month with no issues. i can run the SL report with data for the balance of the month with no problem. if I run the SL Report for the entire month the error occurs. it seems to be some kind of size issue. ANy thoughts on a resolution would be appreciated. The workaround of the split gets us there just timely.
Could the user ever export to Excel from SmartList while using 2013? I rambled on with my answer, but I don't think 2007 and GP 2013 play nice with one another. I was just at a client site last week and they got the same message. We upgraded to Office 2010 and the report exported right away.
Redbeard has pointed you in the right direction.
Good morning all,
I just upgrade to GP 2013 R2, we have used Office 2010 for at least the last 3 years.
The first time I generated this specific SmartList, it exported fine. The second time, I received the error.
I tried the same with a different SmartList, no problem on either count.
I assumed it was a size issue so I began narrowing down content from 1,200 lines to 400 line segments. Still got the error.
So I created an .XLSX document and chose open with Excel always. Still fails.
I have checked nulls on the key ids, still fails.
I noticed some odd characters in SmartLists and in the view these were nulls, however, they were not on all the nulls just a few. I can provide screen shots if you need them.
In SQL I opened the view and found NUMEROUS nulls (50 columns…). I changed the fields to isnull. Then re ran the SmartList and there were no symbols.
After this, I ran the full report, 1,200 lines and no problem exporting.
**SUMMARY - Remove ALL Nulls**
I hope this helps,
Ran into a similar issue today and, in this case, it's special characters to blame. Turns out GP writes the data to an intermediate XML document (as text) and then opens the XML doc with Excel. Excel is balking at the special characters.
Thanks for the additional information.
I ran into this issue again this week and was actually able to resolve. Here's a summary of my troubleshooting and resolution. It probably won't work for everyone, but in my case the corrupt data was in a very small table (SOP10107).
Custom SmartList for Sales/Shipment Tracking Numbers:Sales Transaction History (SOP30200) and Sales Tracking Numbers Work History (SOP10107) are joined to produce sales transactions with tracking numbers.
Of course, deleting and inserting is not recommended, but in this case it was an easy table and the solution worked.
I hope someone can find this useful.
Business Applications communities