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

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (5)

Answers (1)

babubaskaran@outloo... 12 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
babubaskaran@outloo... 12 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No