Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Macro created to forma...
Microsoft Dynamics GP (Archived)

Macro created to format and subtotal data in Excel when exported from Smartlist is truncating data

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello, All:

I am wondering if anyone has seen this issue before:  I have a macro in Excel that has run without issue upon export using the Export Solutions with Smartlist in GP2013.  There is a tab in the worksheet for which I recorded the macro to Select All and Subtotal the data.  However, this month when we ran the query, there are several lines that are excluded from the subtotal and which appear below the Grand Total on that worksheet.

Is there a limitation on the number of lines that can be selected when using a macro that I'm not aware of, or another constraint that I'm unaware of that would cause this issue?  It's exporting all the data, but not subtotaling all data.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Macro created to format and subtotal data in Excel when exported from Smartlist is truncating data

    Redbeard,

    This is exactly what I was thinking must have happened, but was hopeful that there might be a better solution rather than having to re-record the macro that's used to summarize the data.  At least it's a simple macro and not complex.  

    Thank you for your reply, I appreciate your time.

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: Macro created to format and subtotal data in Excel when exported from Smartlist is truncating data

    Melissa,

    When you use Export Solutions, as the data set changes over time, especially if it grows, you might bump into problems like this.  If, when the preparation macro was created, a specific range was defined, instead of all cells, then data might remain, which should have been deleted.

    Similarly, if the completion macro didn't include a big enough range for the data exported, it could make the sub-totals and grand-total inaccurate.  Be very careful with using Export Solutions.

    Babu's suggestion is a good one, but while you are bringing the replacement report online, you should be able to fix your current solution.

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... on at
    RE: Macro created to format and subtotal data in Excel when exported from Smartlist is truncating data

    Hi Melissa,

    You can find all information relating to refreshable excel on the following kb article.  Sorry forgot to copy the link.

    https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;949524

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Macro created to format and subtotal data in Excel when exported from Smartlist is truncating data

    Babu,

    I will have to ask about using the refreshable Excel reports; we do not currently use them.  The problem I have is that this macro was working without issue in previous months, only this last month was it a problem that the data wasn't all included in the subtotal.

    Melissa

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... on at
    RE: Macro created to format and subtotal data in Excel when exported from Smartlist is truncating data

    Hi Melissa,

    Sometime the macro doesn't work in on export solutions in Smartlist.  Have you tried to use the refreshable excel report connected with data connection with GP tables.  I like this way since no need to login to GP and all format is  ready in excel and when you open it will get refreshed and you can just start using the data.

    Thanks,

    Babu

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans