Open Forum

Expand all | Collapse all

Export to Excel from SmartList problem

  • 1.  Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 22, 2019 10:10 AM
    Dear Collaborators,

    Our staff accountant started to run into Excel errors with one SmartList-to-Excel report. No other SmartList gives him these issues. Both of these messages appear once Excel has opened:



    The 2nd message appears over the actual Excel workbook which has all the correct data.

    This happens from the Remote Desktop installation of GP. We're running RemoteApp like good little RDP boys and girls.

    The XML file name doesn't provide much in the way of explanation... and, again, our staff accountant can export other SmartLists to Excel with no problems.

    Any ideas?

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach


    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: Export to Excel from SmartList problem

    TOP CONTRIBUTOR
    Posted Oct 22, 2019 10:26 AM
    @Steve Erbach The error message points to a corrupt excel file.  A few things to try
    1. Have the user try saving to a different location.
    2. Try saving to a local directory on the server and see if there is a network issue causing the file to corrupt.
    3. The other thing is to rename the file and try and do an open and repair in excel.
    Hope one of these thoughts helps!


    ------------------------------
    Kelly Hobart
    Business Data Analyst
    Mount Pleasant Waterworks
    Mount Pleasant SC

    Chapter Leader, South Carolina (Charleston) Chapter
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 22, 2019 11:42 AM
    Hello, @Kelly Hobart!

    ​I'll do my best to answer your questions:

    >> 1. Have the user try saving to a different location. <<

    Not sure where to set that. We're using GP on a Remote Desktop Server, so the when the user exports a SmartList it's saved in a Temp folder in the user's directory on the RDS C: drive. Something like C:\Users\username\AppData\Local\Temp\3... or Temp\4... the directory name changes.

    >> 2. Try saving to a local directory on the server and see if there is a network issue causing the file to corrupt. <<

    The thing we're puzzled about is that it's this one SmartList... it doesn't happen with others. The SmartList has a handful of rows and 6-7 columns.

    >> 3. The other thing is to rename the file and try and do an open and repair in excel. <<

    The file is given the goofy name you see in the message... I don't see where in SmartList setup to alter the way the temp file is named.

    Sorry about not having specific answers. Do any of my responses make sense? Thank you so much for responding!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach


    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: Export to Excel from SmartList problem

    TOP CONTRIBUTOR
    Posted Oct 22, 2019 02:48 PM
    1.  Sorry, should have been clearer on this first one... Save the smartlist to a copy and try exporting that as it will create a differently named file.
    It's possible the same temp file is being overwritten each time they export and if it is already having a corrupt issue then reexporting may reproduce the same error.

    2. Is it possible to log in as the user and try it from a different system to ensure it is user specific and not the terminal server profile alone?

    ------------------------------
    Kelly Hobart
    Business Data Analyst
    Mount Pleasant Waterworks
    Mount Pleasant SC

    Chapter Leader, South Carolina (Charleston) Chapter
    ------------------------------

    Conference-GPUG_200x200


  • 5.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 22, 2019 04:00 PM
    @Kelly Hobart,

    I had my user, Bob, log into the Remote Desktop Server with Windows Remote Desktop​ so that we could look at the folder containing the temp files. He was already logged into GP with RemoteApp.

    At first when the temp folder was full of GP stuff. Then I had him log out of GP and the temp folder emptied out, including the temp XLSX files that were there.

    Then I had him log back into GP via the RemoteApp desktop shortcut I'd given him. GP scatters a passle of temp files of its own into the (previously) empty temp folder. Then he tried that same SmartList and exported it to Excel... and he got the "problem with some content" error on a new file name.

    The temp Excel file has a different name each time he runs that SmartList export.

    I'll have him try logging into a different PC to do this export.

    Thank you, Kelly,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach


    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 6.  RE: Export to Excel from SmartList problem

    TOP CONTRIBUTOR
    Posted Oct 22, 2019 10:40 AM
    Edited by Matthew Arp Oct 22, 2019 10:40 AM
    Steve, what happens if you do not recover the file and then try and open it on another machine?

    ------------------------------
    Matthew Arp
    Business Systems Developer
    Hunton Group
    Houston TX
    ------------------------------

    Conference-GPUG_200x200


  • 7.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 22, 2019 11:44 AM
    @Matthew Arp,

    I had the user answer "No" to the recover question. Excel is opened but there's no workbook opened.

    The mystery deepens! Thank you for taking the time to reply!

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach


    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 8.  RE: Export to Excel from SmartList problem

    TOP CONTRIBUTOR
    Posted Oct 22, 2019 11:47 AM
    The file should still exist, should be in the %temp% directory of the user, you'd probably need to connect to the server's c$ share and navigate to the temp directory for the user.

    ------------------------------
    Matthew Arp
    Business Systems Developer
    Hunton Group
    Houston TX
    ------------------------------

    Conference-GPUG_200x200


  • 9.  RE: Export to Excel from SmartList problem

    TOP CONTRIBUTOR
    Posted Oct 22, 2019 12:00 PM
    Also, you may try removing a column at a time to see if you're able to get the SL to export

    ------------------------------
    Matthew Arp
    Business Systems Developer
    Hunton Group
    Houston TX
    ------------------------------

    Conference-GPUG_200x200


  • 10.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 22, 2019 04:01 PM
    @Matthew Arp,

    I'll have Bob, my user, try removing columns as you suggested. Thanks!

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach


    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 11.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 23, 2019 02:58 AM
    Steve -

    This same issue was happening at another customer site and it was definitely a column formatting issue. I determined it was one of the date fields for us.

    Have a great day!
    Abra Lynne Gilman
    Dynamics GP Practice Lead
    Collins Computing, Inc.


    Conference-GPUG_200x200


  • 12.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 23, 2019 02:52 PM
    Hello, @Abra Gilman, Madam GPUG All Star!

    I have verified that no matter​ who exports that SmartList to Excel, the same errors occur. That's no matter whether the user runs a local copy of GP or runs GP from the Remote Desktop Server. I just did it from my local copy of GP: same two messages.

    I also see that the SmartList is one of the starred (*) reports, meaning that it was distributed with GP. The SQL code is:

    select
    * from (select * from slbSalesOrders with (NOLOCK)) T1

    Nothing out of the ordinary. The SmartList displays just six default fields, with one of them being the Document Date.

    Beats my pair of Jacks.

    Regards,


    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 13.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 23, 2019 08:05 AM
    Hey @Steve Erbach

    this happens on occasion at most of my clients.  The issue may be a formatting issue, but to me, if it still exports and just shows a couple of error messages, you may have a non-issue.  As I was saying, this happens on occasion and it can be anything from permissions to columns formatted incorrectly, but if it opens is it really an issue?

    If it is still too troublesome to deal with you may need to look at whether it is a custom or out of the box SmartList.  ​​

    ------------------------------

    ------------------------------

    Conference-GPUG_200x200


  • 14.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 23, 2019 03:54 PM
    Hi, @Jo deRuiter, another Madam GPUG All Star!

    It's bizarro. No matter whether I export to Excel from a local copy of GP/SmartList or from the Remote Desktop Server installation of GP/SmartList, the same errors appear. The SmartList in question is the ​Sales >> Sales Transactions >> This Week's Returns*... a "built-in" SmartList. It's just weird to think that a built-in SmartList -- which normal users can't Modify -- would cause these problems... especially when there's only one row to export to Excel!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 15.  RE: Export to Excel from SmartList problem

    SILVER CONTRIBUTOR
    Posted Oct 23, 2019 08:38 AM
    When Microsoft switched to their new file format (xlsx, docx, etc.) the added "x" stands for xml.  The files are really zip files with the raw data saved in plain text XML files.  So you can open your xlsx file with something that handles zip files like PKZIP or 7-Zip.  You see a folder called xl, within that a folder called worksheets, and within there you'll see your data saved as xml files.  You can double click on them and IE should open them in a formatted view.  You may be able to see the problematic column/data fairly quickly using this technique.  I don't have a broken one to experiment with but IE maybe complain about the file and even highlight the error to you.  These file could also be opened in any plain text editor like notepad as well, but the advantage with IE is it will format and color code the display making it much easier to read.

    ------------------------------
    Rich LaMarche
    Senior IT Specialist
    Norwich Public Utilities
    Norwich CT
    ------------------------------

    Conference-GPUG_200x200


  • 16.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 23, 2019 03:57 PM
    @Rich LaMarche,

    I grok what you're saying (I like using Microsoft's XML Notepad 2007)... but we've worked up enough spit about this. I found out today that our Accounting Manager said that that's the wrong report to run anyway! So this entire discussion is academic!

    Thank you for taking the time to reply, Rich.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 17.  RE: Export to Excel from SmartList problem

    Posted Oct 23, 2019 12:23 PM
    I have also seen this happen numerous times.  Sometimes I can answer yes, the file will still open, and I pretty much have what I need.  Sometimes that doesn't work and I have to start filtering the SmartList.  Usually, it means there is bad data somewhere.  If I filter to fewer dates for instance, I can get it to export/open just fine.  Although once I swore I ended up successfully pulling all the data but just in smaller chunks.  There was so much data though, I could have missed something. :)  Anyway, you could try filtering out dates or columns and then see if you have bad data in the table it's pulling from.

    ------------------------------
    Stefanie Christiana
    Glidewell Laboratories
    Irvine CA
    ------------------------------

    Conference-GPUG_200x200


  • 18.  RE: Export to Excel from SmartList problem

    GPUG ALL STAR
    Posted Oct 23, 2019 04:21 PM
    @Stefanie Christiana,

    That is a decent approach and one I would normally follow​... but today there's only one row! Six columns! And since our Accounting Manager said that we're going to use a different SmartList, this turns out to be academic.

    Thank you for taking the time to reply!

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role