1

I have a series of data "objects" that are typically used to prepare reports. One class of objects are MS Excel tables. The second set are graphs from a stand alone app (GraphPad Prism) - these can be saved in a variety of formats (I usually save as an EMF). I would like to prepare a word template that has specified locations set where these objects would be imported automatically and their size set to defined parameters (i.e. table set to 2" by 2.25"). Is there a way to create a series of VBA commands that would place six objects in a page and then continue to spool out new pages as needed until the list of objects has been processed. Here's an image of how one page would appear:

Here's my image

Reviewing older posts I have seen references to mail merge. Seems odd to use something designed for email(?). Is this the route to take or is there a more elegant way to do this. Or is there ANY way to do this - even if it's outside of the MS family of apps.

Improve this question
2
  • In poking around, I noticed the following. I typically paste my excel table into Word as a MS Excel Worksheet (code) Object. After the paste is complete, I can right click and receive several options including "macro-enabled worksheet object" with the submenu options "edit", "open" and "convert". I'm certainly no expert, but seems this must be the route to everlasting happiness?
    – Supafied
    Jun 13, 2016 at 0:04
  • Because these graphs are pasted as Worksheet (code) Objects, I can open them and manually set the offset counter. So now if I knew how to code vba to edit each object to specify the source file, which cell to update with the loop counter output, and how to step sequentially through the objects in a document (i.e. move through each graph starting at the beginning and moving to the end of the document), I think I would have half the features I'm looking for.
    – Supafied
    Jun 13, 2016 at 0:21

1 Answer 1

Reset to default
0

It's easy to make an output like this in Graphpad Prism if that's a possibility for you.

  1. Enter your data in the first table (Default name = "Data 1")
  2. Analyze using Nonlinear regression. This yields a table "Nonlin fit of Data 1".
  3. Select Graphs > Data 1
  4. Reformat the graph so it contains only the first four curves you want shown.
  5. Duplicate the graph to create "Copy of Data 1". In the file tree, change the name to "Data 2" - this will change the title of the graph. Reformat to show the second set of data.
  6. Repeat step 5 with the third set of data.
  7. Go to "Layouts". Choose the Layout with three graphs. Add your graphs to the Layout. Resize them and move them to the right hand side of the worksheet.
  8. Go to the sheet "Nonlinear fit of Data 1".
  9. Select the results for the first four compounds and copy them to the left of the first graph. Double-click the results box to reformat font size and add row labels.
  10. Repeat step 9 for the results of rest of the compounds. The final Result is shown below.
  11. Lastly, replace the data on page 1 with zeroes and save the file as a template. The scientists can now put new data into the template every week, and have the analysis done and the graphs drawn automatically.
  12. It's possible to export the Analysis results using the "Export" key. (Sorry, I don't know how to automate this.)

The final result will look like this:

Prism Layout

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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