Ask a new question

Help Merging Charts and Graphs from Excel into Word

Hi all,

I'm a 6th grade teacher in a low-income school district, and in an effort to keep our parents informed of their child's progress in reading, we've decided to send home monthly reading progress trackers that contain all of the most recent data on student reading.  We need this report to be highly visual, since most of our parents do not speak or read English well.  Thus, we created a highly visual template, but because our school teaches over 400 students, we need a way to merge the template with our database of students' reading data (in Excel), so that we won't have to make a new report for each student.

I have recently learned the basics of mail merge, and I've even gotten the table to work as well, but I am still unable to merge charts or bar graphs generated in Excel.  I tried to download Doug Robins's mail merge program, but I couldn't seem to find the proper location in which to save the file (my folder network didn't have a "Startup" folder), so I'm not seeing the additional ribbon to allow me to merge a chart.

If Mr. Robins or anyone else skilled in these sort of merges could take a look at my templates on my dropbox folder (see link below) and offer any suggestions, I'd greatly appreciate it.  Again, this could really help our students and their families to prioritize literacy, and our first trackers are supposed to be sent home in 6 weeks, so time is of the essence!

https://www.dropbox.com/sh/ebnlyn3cq769qm1/4zw5X8YfLZ

In terms of specific questions, here are a few:

1) Is there any way to format the cells of the table created in Microsoft Word, such that I could create conditional formatting to color the cells?

2) How can I have numbers in the table only go to one decimal place? I can't seem to format the cells in the Word table in the same way I could in Excel.

3) Is it possible to add code to place the reading percentile text box in the proper location on the 2nd page?

4) Is there any hope to adding a graph similar to the first line graph in the template generated only from the reading data?

N.B. All of the student data is made up, and I could add, remove, or modify any field in the Excel spreadsheet.  What I currently have is just a template that I created to start messing around with this project.

Thanks,

Matt Smith
6th Grade History Teacher & Debate Coach
KIPP Heartwood Academy
(603) 520-2903


I will look at your documents a little bit later.

 

However, in the meantime to fine the Word Startup folder, in Windows Explorer, you may need to click on the Organize dropdown in the bar at the top of the window and then click on Folder and Search Options and go to the View tab of the Folder Options dialog and select the radio button for "Show hidden files, folders and drives"

 

The Word Startup folder will be at the following location:

 

C:\Users\[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

 

where [User Name] will be the name of the account with which you log-on to your computer.



Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
It's time to replace ‘Diversity, Equity & Inclusion’ with ‘Excellence, Opportunity & Civility’ - V Ramaswamy

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Matt,

 

Unfortunately, there seems to be something amiss with the Reading Tracker_Draft3 (1) trying to merge.docx document as, while I can get it to switch from Draft View to Web Layout view, in which the elements of the mock-up of the charts are scattered at random, I cannot get it to switch from Draft view to Print Layout view.

 

To answer your specific questions:

 

  1. With a custom build application, it would be possible to apply conditional formatting to color cells of a table based on the values insert into those cells.  In Word however it is not possible to do it in a way similar to that which is available in Excel.
  2. If Alt+F9 is used to toggle the display of field codes, the following formatting switch can be added to a mergefield to cause numbers to be displayed to 1 decimal place:

    { MERGEFIELD "field name" \# "0.0" }

    See the following page of fellow MVP Graham Mayor's website for more information on the use of formatting switches - "Formatting Word fields with switches" at

    http://www.gmayor.com/formatting_word_fields.htm
  3. As the reading percentile box is common across all of the students, the best way to handle that would be to position it manually in the mail merge main document.
  4. If the data points are available in the data source, it would be possible to create an individual line graph for each student similar to the first one in the Reading Tracker_Draft3.pdf

    I do not however see the necessary data points in the 6th Grade Reading Database - Take 1.xlsx.

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
It's time to replace ‘Diversity, Equity & Inclusion’ with ‘Excellence, Opportunity & Civility’ - V Ramaswamy

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi again Doug,

Thank you so much for the helpful replies.  I've modified the template slightly and have everything working properly except for the conditional formatting macro.  As mentioned before, in my table, I would like certain values assigned a red cell background, and others assigned a green cell backgroun.  
Here is what my friend has helped me write so far:


Sub formattingtable()
    Dim oTbl As Table
    Dim oCel As Cell

    For Each oTbl In ActiveDocument.Tables
        j = 1
        i = 1
        For Each oRow In oTbl.Rows
            For Each oCel In oRow.Cells
                
                If j = 2 Then
                    If Val(oCel) = 100 Then
                       oCel.Shading.BackgroundPatternColorIndex = wdWhite
                    End If
                End If

            Next oCel
            j = j + 1
        Next oRow
    Next oTbl
End Sub


He can't seem to find a command that reads a numerical value inside an individual cell (see the emboldened line in the macro) however. Does such a command exist?  If you could provide any insight into how to fix this macro, I would greatly appreciate it.

Thanks again for all of the helpful suggestions you have provided already!

Best,
Matt Smith
6th Grade Social Studies
KIPP Heartwood Academy
(603) 520-2903

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated December 21, 2023 Views 2,868 Applies to: