Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SmartList Export - Exc...
Microsoft Dynamics GP (Archived)

SmartList Export - Excel cannot open the file...

(0) ShareShare
ReportReport
Posted on by Jim Lines 2

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

  • Suyash Thomson Profile Picture
    Suyash Thomson 5 on at
    RE: SmartList Export - Excel cannot open the file...

    Hi,

    If you have tried all possible methods to repair your Excel file but none of them work, then maybe your Excel file is not corrupted, but you just don’t have permission to open it on your system.

    If you don’t have permission to open the Excel file, it mayl display the error "Excel cannot open the file because the file format or file extension is not valid." I have tested this on my Windows 10.  You can use the following steps to grant everyone on this computer permission to open the Excel file.

    Right-click the Excel file that cannot be opened and select Properties.

    After the file’s Properties dialog opens, select the Security tab, and then click the Edit button.

    After the file’s Permissions dialog opens, click the Add button.

    When the Select Users or Groups dialog opens, click the Advanced button. When the next dialog opens, click Find Now so all users and groups will show up in the search results list at the bottom of the dialog. Select the Everyone group from the list, and then click OK. Click OK again.

    When you return to the Permissions dialog, you will see the Everyone group has been added to the group or user list. Select the Everyone group, check all checkboxes under Allow, and then click Apply.

    That’s it. Now check if you can open the Excel file without any problems. Also, You can check the following mentioned thread URL:

    social.technet.microsoft.com/.../corrupted-excel-file-repair    

    Best Regards,

    Suyash

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: SmartList Export - Excel cannot open the file...

    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!  

  • Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: SmartList Export - Excel cannot open the file...

    Thank you for the update and information. I'm sure it'll help others and good information to have public.

  • Verified answer
    Jim Lines Profile Picture
    Jim Lines 2 on at
    RE: SmartList Export - Excel cannot open the 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.

    Summary:

    • User selected a range of SOP Orders from 1/16/2017 to 1/21/2017 and upon export, the famous “Excel cannot open the file…” error message.
    • I removed the tracking number column and was able to export the data.  (Problem lies in Tracking data)
    • Ran SL with only the tracking number column (less columns, faster results, easier isolation). Still get error.
    • Ran SmartList for each day in the range.  Found an issue on 1/17 and 1/18.  By process of elimination, I was able to isolate two records.
    • Those two records have a 22-character tracking number which is much longer than the normal ones in the dataset.  
    • Thinking the length of the tracking # may be the problem, I updated those records replacing the tracking # with “TEST.”   Still receiving same error. 
    • Created a new SmartList using only the Sales Tracking Numbers Work History table.  Still get export error.  So I’ve narrowed it down to two records in the SOP10107 table causing the issue.  
    • Since the SOP10107 table is very simple (SOP #, SOP Type, Tracking Number), I felt comfortable deleting the records and then inserting them back.  This resolved the problem.  So even though I could not see any signs of data corruption, it appears something in those records was the cause.

    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.  

  • Community Member Profile Picture
    Community Member on at
    RE: SmartList Export - Excel cannot open the file...

    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".

  • Kristie McNulty Profile Picture
    Kristie McNulty 977 on at
    RE: SmartList Export - Excel cannot open the file...

    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.

  • Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: SmartList Export - Excel cannot open the file...

    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.

    Thank you,

  • Kristie McNulty Profile Picture
    Kristie McNulty 977 on at
    RE: SmartList Export - Excel cannot open the file...

    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.

  • Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: SmartList Export - Excel cannot open the file...

    Thanks for the additional information.

  • Community Member Profile Picture
    Community Member on at
    RE: SmartList Export - Excel cannot open the file...

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,407 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans