more-arw search

Q&A Forum

Macros to help getting reports from Microsoft dynamics

Hello, Can any one please provide any macros to get reports from microsoft dybamics into excel i.e. i am currently using macros to transfer txt file from microsoft dynamics into excel and working on it. but if someonah has already some tools on this, it would be great to have it for my help thank you in advance,

Answers

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

I've downloaded data from accounting systems into spreadsheets since the early 1980s, and it can be a very powerful and easy way to create and update reports and analyses. So you're taking the right approach.

Tell me about the text files you're downloading from Dynamics...Are they comma-separated values (CSV)? Tab-delimited? Or are they formatted reports?

If you can download your data as *data*--that is, as CSV or tab-delimited--you really don't need macros. In fact, macros would only get in your way.

Also, what data are you downloading? A GL Trial Balance? Sales history? Check register? Or something else?

(Agent, JKS Solutions, Inc.) |

Your question is probably on the right track but your question is not stated correctly and leads me to believe you don't have a good understanding of what a Macro is or does. An Excel macro cannot get data to jump from a database into Excel. Macros are coded in VBA language but run against data already in a spreadsheet.

You may also be misusing the term Macro, when what you need to refer to is VBA Application Coding that may reside inside an Excel Code Module that has been set up to extract data from a specified downloaded report that might be either in text file format or in a version of excel, as Charlie seems to have mentioned.

The best way for you to start your adventure in coding is to use the "Record Macro" feature on the "Developer" ribbon tab, that you will have to activate from the options button. This will allow you to record all your steps inside excel, and then you can edit your code in the VBA Module.

If you are trying to access files or other MS applications or using SQL Server links via ODBC connections, you will need a much more advanced understanding of Code structure.

You might be trying to do something conceptual that you aren't ready to execute. Guessing you might have some luck talking to your IT group. There is an Excel coding group on LinkedIn that might have some people who could further your learning.

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

Valerie,

I agree, I too believe the wrong term is being used.

As for MS Dynamics and ODBC, the version I was used to was not that difficult to create a link. The first time or two out of the box may be a bit confusing, but with a little trial and error one can find the correct files to bring into the SQL statement.

However, if you don't really understand SQL, and not withstanding the MS wizard, you'll have more difficulty.

I made some interesting spreadsheets linked (one-way only) to Dynamics.

Anonymous
(Controller) |

If you are using the report writer FRX with your Microsoft dynamics you can set the "output" in excel format rather than viewer or printed.. You might want to reach out to your MS dynamics consultant for more details on how to set these up. Then you determine the folder they will be directed to. You can also chain reports together for further calculations.

Roxanne Orticke
Title: Senior Director of Finance
Company: NACHA
(Senior Director of Finance, NACHA) |

An even easier way to download data from MS Dynamics to Exel is SMARTLISTS, a list-ready module within Dynamics. You just pick what columns you need to see with pre-organized sets.
It is not as sophisticated as FRX reporting, yet is structurally beyond .csv downloading.
SMARTLIST is a baseline option in the software...I can't remember off the top of my head the click-through-tree to get to the option, but is relatively easy to find using Dynamic's HELP.

Anonymous
(Finance Manager) |

Another question would be what type of data and what type of reports are you trying to generate?

I have direct links into my Dynamic Tables to create dashboards and other reporting for Senior management and I also have SQL reports built as well which can give them Automated (subscription based) reports that go out as an excel attachment at the interval they request.

What version of Dynamics are you using (SL, GP, AX NAV)

Bob Scarborough
Title: CEO
Company: Tensoft, Inc.
(CEO, Tensoft, Inc.) |

Most of the Dynamics financial / ERP systems have Excel based reporting - where you can print to excel. The excel files build the connection to the database in automatically without addition effort on your part.

There is really no macro needed - it is something you can setup through excel and your ERP deployment model. You use the data function in Excel if you are setting this up on your own.

Matt Jackson
Title: President
Company: OpsVantage
LinkedIn Profile
(President, OpsVantage) |

I did a consulting gig for a Microsoft team that was using Dynamics to run part of their business. They did a system selection process that put internal solutions head to head with external solutions and they selected a product called Zap. It takes some work to get up and running but the reports are beautiful and fast.

3983 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.