1

I recently started at a company as a Automation Developer. My job is to automate periodic reports (in Word) for all of our customers (~50), one for each. Each report is slightly different, so I am going to have develop code for each company. The position is new and there are no automation in place; all reports are compiled by copying data from Excel, pasting, and formatting in Word.

However, the data in all these reports all come from just 2 IT systems. I can set up the system to automatically send me Excel reports for each customers on.

Here is my automation process for generating each report.

  1. Create a template of the report which are striped away of tables, dates, and statistics.
  2. Collect all data into one Excel workbook. (Different tables are in different worksheets.)
  3. Ensure all tables are in a standard format.
  4. Run a VBA script to compile all tables into a Word doc and populate dates and statistics.

Questions.

  1. Any recommendations for improving my current process?

  2. What best practices should I follow when using VBA in Excel to automate reports?

  3. How best to handle future changes?

  4. Best way to transfer code amongst different users? And my users are not VBA-savvy; I will have to document how to automate these reports step-by-step.

closed as too broad by gnat, Robert Harvey, Doc Brown, Bart van Ingen Schenau, TMN Jun 28 '16 at 12:53

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Hy GTyler, your approach sounds fine, so if it works smooth for you, I do not see any problem which we can solve for you. Questions just asking for "best practices" or "I don't have a problem, but how can I improve?" will typically be closed on this site as "too broad". However, if you can narrow down your question and focus on the details of a problem you might have, then your question has chance to survive. – Doc Brown Jun 28 '16 at 6:40
  • ... so to 1. Nope, no recommendations. 2. Could write a whole book about this, too broad. 3. similar as with any program: use version numbers, use version control, keep a strict separation between the program and user data, keep a change log. 4. You deliver each new version of the Excel document to your users (which you have separated from the data, see 3) by the established way of your company (common network drive, email, intranet, installer package, sharepoint server, whatever you like). – Doc Brown Jun 28 '16 at 6:46
4

Gain control of how report data is formatted.

Excel simply isn't a data format. It has it's own presentation ideas. You can make this work and it sounds like a lot has already been built help you do this. So maybe you don't want to swim against the tide at this point but you're tied to a lot of things you don't want to be tied to. Different versions of excel come out, different versions of VB savvy users come out. Security concerns dictating disabling user access to VB scripts come out... on and on.

If the report is meant to be in MS word find the most direct way to put it into MS word. If an excel report is also required find the most direct way to also put it in excel. Using one to leverage the other invites Rube Goldberg and Murphy to come over and have party.

The 2 IT systems are where you should be getting reports from. Get the most basic, flexible format from them you can and use that to make what you need.

  • From the OPs statement "I can set up the system to automatically send me Excel reports for each customers on" I conclude the OP is already in control of how the data is formatted, so though he did not clearly state where his problem is, that does not seem to be his problem. – Doc Brown Jun 28 '16 at 9:20
  • 2
    @DocBrown yet we hear talk of using asking users to use excel scripts to write word. That's not control. That's taking what you get and trying to live with it. – candied_orange Jun 28 '16 at 9:23
  • I don't understand what you are trying to tell me. IMHO the fact the formatting program is written in Excel VBA has absolutely nothing to do with the control the OP has about the input data - these are two different things. – Doc Brown Jun 28 '16 at 9:26
  • 1
    @DocBrown I'm trying to tell you that using one report to generate another report is an antipattern. Especially if you're making users do it. We're told there are two IT systems where data is coming from. That's what reports should be generated from. Using VBA or something else. Users shouldn't be doing this. – candied_orange Jun 28 '16 at 9:34
  • 1
    @charlotte I'm leaning the same way. I don't like users seeing excel if excel represents a report in the making. They'll just think of it as another report. If excel is hidden from them then it's just another tool for the system to use. – candied_orange Jun 29 '16 at 17:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.