Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
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 are in the process of upgrading to GP2015 (with March hotfix) and we are having same/similar SmartList export issues for custom SmartList queries.
Can anyone in this discussion confirm if, at the time of exporting your SmartList results, the error message you receive contains what appears to be a date-timestamp.xlsx filename?
We are also using terminal services where we are deploying Excel 2013. If we export the first 1,000 rows, no issue, data exports. If we increase rows to 100K, that's when the error message appears. (and because it makes it easier for us to search these boards, here is the entire error message: Excel cannot open the file 2015514-123648.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.)
So is it NULLS, special characters, Excel version...all-of-the-above...that is the issue here? The same SmartList works in GP2010 where we have Excel 2007 deployed, again, through terminal services.
Thank you in advance for any/all guidance.
With the "Excel cannot open the file xxxxxx.xlsx because the file format or file extension is not valid" error, we've seen this caused by the Excel reports being corrupted and needing to be re-deployed, third party products causing issues, etc.
I myself have seen this error for two different reasons:
1. It can be caused by a special character in one of the columns (like Customer Name) or in a field that is being exported. It is not the common special characters such as *, !, #, %, etc. It is when you use integration manager typically and it converts a character to a wing ding symbol and Excel doesn’t recognize that on export. To find the issue, it takes some redundant trouble shooting to find the cause. You know the error occurs at 500 records but not 400 records. I would first split the difference and have 450 results returned and export. If it exports without issue, you know that the troubled record is between 450 and 500 so try exporting 475. If that fails, then you know the issue is between 450 and 475 and so forth. You may be able to spot the issue just by having SmartList return the 500 records, than expand the columns so you can see all the field values and see if there are any records that stand out to you with a special character, typically at the end of the record name and typically in the customer name field.
2. I have also seen this error if you are trying to export extremely large files. Smartlist functionality is not designed to export more than 5000-6000 lines to Excel. Because Smartlist is limited to the number of lines you are exporting in design Excel List Builder. Excel List Builder is designed to handle the large number of line (40,000). You will need to talk to your IT department and have them publish the Excel List Builder version of you Account Transactions. This way it will automatically create an Excel report present on your desktop that will refresh with current information
Let us know if this helps at all or if you have further questions.
Thanks, we will debug, but seems SmartList has put the unique characters in on its own because the 23,000+ rows of data export without issue from GP2010/Excel 2007. We will explore the Excel List Builder to see if it helps resolve if all else fails. Thanks for the time and feedback.
She may have another program other than Excel as the default program to open .xlsx files. Browse to an xlsx file right click and open with Excel, check the box "Always use the selected program to open this kind of file".
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.
Thank you for the update and information. I'm sure it'll help others and good information to have public.
This was awesome! My issue was #1. Your information and troubleshooting suggestion helped me to find the "wing ding" that caused the error. You rock!
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.
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.
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,
Business Applications communities