none
Use VBA To Creae A Microsoft Word Chart RRS feed

  • Question

  • When a Microsoft Word chart is created the process also automatically creates an Excel sheet1 table. The contents of this table are correctly charted by the following program. I need to be able to use VBA to add data to the table and to chart this new information.

    The test program is as follows:

    Dim WordDoc As Object
    Dim WordApp As Object
        Set WordApp = CreateObject("Word.Application")
        With WordApp
            .Visible = True
            .WindowState = wdWindowStateMaximize
            .Documents.Add
            Set WordDoc = .ActiveDocument
        End With
        
        
    WordDoc.InlineShapes.AddChart Type:=xlLineMarkers
    With WordDoc.InlineShapes(1).Chart.ChartData
     .Activate
    End With

    WordDoc.InlineShapes(1).Chart.HasTitle = True
    WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "This is a test"

    'The following do not change the chart data

                Columns("D").Delete
                Columns("C").Delete
                

                Cells(1, 1) = "Date"
                Cells(1, 2) = "Glucose"
                Cells(2, 1) = "2/3/2015"
                Cells(2, 2) = "105"
                
    ActiveChart.SetSourceData Source:=Range("'Chart Data'!A1:B2")



    RERThird

    Sunday, August 9, 2015 7:01 PM

Answers

  • Hi RER

    This is word 2007 or later, I take it?

    First thing you should do is change this:

           .Documents.Add
            Set WordDoc = .ActiveDocument

    to

           Set WordDoc = .Documents.Add

    You need to always refer to the relevant object in order to work with it, just as you use WordApp and WordDoc to speak to Word objects. The code you show us that doesn't work refers to nothing at all...

    Here's a bit of sample code I have that should get you started. Notice how it declares and assigns to objects in order to work with them. You can use late binding (declare as Object) or early binding so that you get Intellisense (use the commented declarations, in that case).

    Sub Chart2007()
        Dim ils As word.InlineShape
        Dim c As word.Chart
        Dim wb As Object 'Excel.Workbook
        Dim ws As Object 'Excel.Worksheet
        Dim lo As Object 'Excel.ListObject
        
        Set ils = ActiveDocument.InlineShapes.AddChart _
                  (xlColumnStacked, Selection.Range)
        Set c = ils.Chart
        Set wb = c.ChartData.Workbook
        Set ws = wb.Worksheets(1)
        Set lo = ws.ListObjects(1)
        lo.Resize wb.Application.Range("A1:D7")
        With ws
          .Cells(6, 1).value = "New category"
          .Cells(6, 2).value = 6.8
        End with
    End Sub


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, August 10, 2015 3:20 PM
    Moderator
  • Hi Robbie,

    It is blank document contain the macro, you need to download the document and then run the macro. And here is the code for your reference:

    Sub CreateChart()
    Set WordDoc = ActiveDocument
         
    Dim ils As InlineShape
     Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
     With WordDoc.InlineShapes(1).Chart.ChartData
      .Activate
     End With
    
     WordDoc.InlineShapes(1).Chart.HasTitle = True
     WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "This is a test"
    
    
     Set c = ils.Chart
        Set wb = c.ChartData.Workbook
        Set ws = wb.Worksheets(1)
        Set lo = ws.ListObjects(1)
        lo.Resize wb.Application.Range("A1:D7")
        With ws
          .Cells(6, 1).Value = "New category"
          .Cells(6, 2).Value = 6.8
        End With
    wb.Close
     
    
    End Sub
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 18, 2015 8:45 AM
    Moderator
  • Thanks to the excellent assistance of Cindy and Fei the VBA program to create and display a Microsoft Word Chart finally works.

    This program uses the 64 bit versions of Microsoft Office 2013 ( Access, Word and Excel).

    The VBA code is as follows:

    Sub TestGraph_Click()


    10 Dim WordDoc As Word.Document
    20 Dim WordApp As Word.Application
    30    Set WordApp = CreateObject("Word.Application")
    40    With WordApp
    50        .Visible = True
    60        .WindowState = wdWindowStateMaximize
    70        .Documents.Add
    90    End With
        
    100 Set WordDoc = WordApp.ActiveDocument
        WordDoc.Activate
         
    110 Dim ils As InlineShape
    120 Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
    130 With WordDoc.InlineShapes(1).Chart.ChartData
    140  .Activate
    150 End With

    152 WordDoc.InlineShapes(1).Chart.HasTitle = True
    154 WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "Glucose"

    Dim c As Word.Chart
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim lo As Excel.ListObject

    160    Set c = ils.Chart
    170    Set wb = c.ChartData.Workbook
    180    Set ws = wb.Worksheets(1)
    190    Set lo = ws.ListObjects(1)
    200    lo.Resize wb.Application.Range("A1:B6")
        
     With ws
                .Columns("D").Delete
                .Columns("C").Delete
                .Columns("B").Clear
                .Columns("A").Clear

                .Cells(1, 1) = "Date"
                .Cells(1, 2) = "Glucose"
                .Cells(2, 1) = "2/3/2015"
                .Cells(2, 2) = "105"
                .Cells(3, 1) = "3/3/2015"
                .Cells(3, 2) = "90"
                .Cells(4, 1) = "4/3/2015"
                .Cells(4, 2) = "75"
                .Cells(5, 1) = "5/3/2015"
                .Cells(5, 2) = "95"
                .Cells(6, 1) = "6/3/2015"
                .Cells(6, 2) = "102"
     
     End With
     
     WordApp.WindowState = wdWindowStateMinimize
     WordApp.WindowState = wdWindowStateMaximize
        
    End Sub

    Robert Robinson


    RERThird

    • Marked as answer by RERThird Wednesday, August 19, 2015 1:57 AM
    Wednesday, August 19, 2015 1:57 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft word, I'll move your question to the MSDN forum for Word

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=worddev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs. Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, August 10, 2015 6:04 AM
  • Hi RER

    This is word 2007 or later, I take it?

    First thing you should do is change this:

           .Documents.Add
            Set WordDoc = .ActiveDocument

    to

           Set WordDoc = .Documents.Add

    You need to always refer to the relevant object in order to work with it, just as you use WordApp and WordDoc to speak to Word objects. The code you show us that doesn't work refers to nothing at all...

    Here's a bit of sample code I have that should get you started. Notice how it declares and assigns to objects in order to work with them. You can use late binding (declare as Object) or early binding so that you get Intellisense (use the commented declarations, in that case).

    Sub Chart2007()
        Dim ils As word.InlineShape
        Dim c As word.Chart
        Dim wb As Object 'Excel.Workbook
        Dim ws As Object 'Excel.Worksheet
        Dim lo As Object 'Excel.ListObject
        
        Set ils = ActiveDocument.InlineShapes.AddChart _
                  (xlColumnStacked, Selection.Range)
        Set c = ils.Chart
        Set wb = c.ChartData.Workbook
        Set ws = wb.Worksheets(1)
        Set lo = ws.ListObjects(1)
        lo.Resize wb.Application.Range("A1:D7")
        With ws
          .Cells(6, 1).value = "New category"
          .Cells(6, 2).value = 6.8
        End with
    End Sub


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, August 10, 2015 3:20 PM
    Moderator
  • Hi Cindy,

    Thank you very much for your reply and the corrected code.

    I believe that I need to use VBA to create an Excel application prior to running the code, but cannot produce the Word Chart.

    Best regards,

    Robbie


    RERThird

    Wednesday, August 12, 2015 1:03 PM
  • Hi Robbie

    No, Word starts up Excel for you, on this line

    Set ils = ActiveDocument.InlineShapes.AddChart _
                 
    (xlColumnStacked, Selection.Range)


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, August 12, 2015 2:15 PM
    Moderator
  • Hi Cindy,

    Thank you again for your assistance.

    I am doing something wrong.

    When I execute the code from a "TestGraph" Button subroutine line 10 has an error message #450.

    Wrong number of arguments or invalid property assignment.

    The subroutine is correctly entered. Office is 2013 64 bit.

    Private Sub TestGraph_Click()



    Dim msgBoxReturn As Integer

    On Error GoTo ErrorHandler

    10    Dim ils As Word.InlineShape
    20    Dim c As Word.Chart
    30    Dim wb As Object 'Excel.Workbook
    40    Dim ws As Object 'Excel.Worksheet
    50    Dim lo As Object 'Excel.ListObject
        
    60    Set ils = ActiveDocument.InlineShapes.AddChart(xlColumnStacked, Selection.Range)
    70    Set c = ils.Chart
    80    Set wb = c.ChartData.Workbook
    90    Set ws = wb.Worksheets(1)
    100    Set lo = ws.ListObjects(1)
    110    lo.Resize wb.Application.Range("A1:D7")
    120    With ws
    130     .Cells(6, 1).Value = "New category"
    140      .Cells(6, 2).Value = 6.8
    150    End With

    Exit Sub
        
    ErrorHandler:
    msgBoxReturn = MsgBox("Error Source: Excel Sheet/Chart" & vbCrLf & "Error Line: " & Erl & vbCrLf & "Error Number: " & Err.Number & vbCrLf & Err.Description, vbCritical, "")
    Err.Clear

    End Sub



    RERThird

    Wednesday, August 12, 2015 3:22 PM
  • I don't see how it could be line 10 as that is simply a declaration...

    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, August 12, 2015 3:28 PM
    Moderator
  • Hi Cindy,

    It should not have made any difference, but I tried moving the Dims up to the beginning of the subroutine and the same error message is now at line 60.

    What puzzles me is that I don't think that an Active document has been created.

    In tools, references have been checked for object library for Microosft Excel, Microsoft Graph and Microsoft Word.

    Thank you again.

    Robbie


    RERThird

    Wednesday, August 12, 2015 3:55 PM
  • Hi Robbie

    The sample code I gave you is meant to run from Word, with the document active in which you want to create the graph - that's "ActiveDocument". As the name of the procedure says, it was written in/for Word 2007.

    I'm currently on a 2010 machine...

    Why don't you look in the object browser (F2) what the 2013 AddChart method wants in the way of arguments?

    Also, note that xlColumnStacked is probably part of the EXCEL object model, so you either need to add a Reference to the Excel library in your project OR look up the integer equivalent to xlColumnStacked and use that instead of the enum constant.


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, August 12, 2015 4:08 PM
    Moderator
  • Hi Cindy,

    Your assistance is greatly appreciated, but I am still having no success and will have to defer further work until time is available in the future.

    Thank you again.

    Robbie



    RERThird

    Wednesday, August 12, 2015 6:51 PM
  • Hi Robbie,

    To change the data source of chart in Word document, we can get the workbook via chart object from shape like Cindy suggested. And then get the workbook to manipulate the data in this workbook.

    I also write a demo based on the code mentioned by Cindy in the preview post, you can download it from below:
    http://1drv.ms/1UEkYjq

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Thursday, August 13, 2015 7:03 AM
    Moderator
  • Hi Fei,

    Thank you for the follow-up. I tried the link, but the document is a blank.

    Would you please post your code suggestions on this forum.

    Thank you very much.

    Robbie



    RERThird

    Thursday, August 13, 2015 12:46 PM
  • Hi Robbie,

    It is blank document contain the macro, you need to download the document and then run the macro. And here is the code for your reference:

    Sub CreateChart()
    Set WordDoc = ActiveDocument
         
    Dim ils As InlineShape
     Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
     With WordDoc.InlineShapes(1).Chart.ChartData
      .Activate
     End With
    
     WordDoc.InlineShapes(1).Chart.HasTitle = True
     WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "This is a test"
    
    
     Set c = ils.Chart
        Set wb = c.ChartData.Workbook
        Set ws = wb.Worksheets(1)
        Set lo = ws.ListObjects(1)
        lo.Resize wb.Application.Range("A1:D7")
        With ws
          .Cells(6, 1).Value = "New category"
          .Cells(6, 2).Value = 6.8
        End With
    wb.Close
     
    
    End Sub
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 18, 2015 8:45 AM
    Moderator
  • Hi Fei,

    Thank you very much for the reply and the code.

    This is what I am trying:

    Sub TestGraph_Click()

    On Error GoTo ErrorHandler


    10 Dim WordDoc As Object
    20 Dim WordApp As Object
    30    Set WordApp = CreateObject("Word.Application")
    40    With WordApp
    50        .Visible = True
    60        .WindowState = wdWindowStateMaximize
    70        .Documents.Add
    90    End With

    100 Set WordDoc = ActiveDocument

    110 Dim ils As InlineShape
    120 Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
    130 With WordDoc.InlineShapes(1).Chart.ChartData
    140  .Activate
    150 End With

    152 WordDoc.InlineShapes(1).Chart.HasTitle = True
    154 WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "This is a test"


    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim lo As Excel.ListObject

    160 Set c = ils.Chart
    170    Set wb = c.ChartData.Workbook
    180    Set ws = wb.Worksheets(1)
    190    Set lo = ws.ListObjects(1)
    200    lo.Resize wb.Application.Range("A1:D7")
    210    With ws
    220      .Cells(6, 1).Value = "New category"
    230      .Cells(6, 2).Value = 6.8
    240    End With
    250 wb.Close

    ErrorHandler:
    msgBoxReturn = MsgBox("Error Source: Excel Sheet/Chart" & vbCrLf & "Error Line: " & Erl & vbCrLf & "Error Number: " & Err.Number & vbCrLf & Err.Description, vbCritical, "")
    'Err.Clear
    'objExcel.Quit.Quit SaveChanges:=wdDoNotSaveChanges

    End Sub

    Execution of the program results in a brief display of the correct chart followed by an Excel crash with the following message:

    Problem signature:
      Problem Event Name: APPCRASH
      Application Name: EXCEL.EXE
      Application Version: 15.0.4745.1000
      Application Timestamp: 55a4bb73
      Fault Module Name: EXCEL.EXE
      Fault Module Version: 15.0.4745.1000
      Fault Module Timestamp: 55a4bb73
      Exception Code: c0000005
      Exception Offset: 00000000003d0840
      OS Version: 6.3.9600.2.0.0.272.7
      Locale ID: 1033

    Thank you again.

    Best regards,

    Robbie


    RERThird

    Tuesday, August 18, 2015 12:49 PM
  • Hi Robbie

    What happens if you comment out everything to do with error handling (including trying to Quit.Quit Excel, which isn't valid)?


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, August 18, 2015 3:32 PM
    Moderator
  • Hi Cindy and Fei,

    Thank you again for your assistance.

    I had already commented out

    err.clear

    objExcel.quit...

    without any change.

    This is the current subprogram:

    Sub TestGraph_Click()

     


    'On Error GoTo ErrorHandler


    10 Dim WordDoc As Word.Document
    20 Dim WordApp As Word.Application
    30    Set WordApp = CreateObject("Word.Application")
    40    With WordApp
    50        .Visible = True
    60        .WindowState = wdWindowStateMaximize
    70        .Documents.Add
    90    End With

    100 Set WordDoc = WordApp.ActiveDocument
        WordDoc.Activate

    110 Dim ils As InlineShape
    120 Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
    130 With WordDoc.InlineShapes(1).Chart.ChartData
    140  .Activate
    150 End With

    152 WordDoc.InlineShapes(1).Chart.HasTitle = True
    154 WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "This is a test"


    'Dim wb As Excel.Workbook
    'Dim ws As Excel.Worksheet
    'Dim lo As Excel.ListObject

    '160 Set c = ils.Chart
    '170    Set wb = c.ChartData.Workbook
    '180    Set ws = wb.Worksheets(1)
    '190    Set lo = ws.ListObjects(1)
    '200    lo.Resize wb.Application.Range("A1:D7")
    '210    With ws
    '220      .Cells(6, 1).Value = "New category"
    '230      .Cells(6, 2).Value = 6.8
    '240    End With
    '250 wb.Close


    'ErrorHandler:
    'msgBoxReturn = MsgBox("Error Source: Excel Sheet/Chart" & vbCrLf & "Error Line: " & Erl & vbCrLf & "Error Number: " & Err.Number & vbCrLf & Err.Description, vbCritical, "")
    'Err.Clear
    'objExcel.Quit.Quit SaveChanges:=wdDoNotSaveChanges

    End Sub

    I commented out all of the Excel sections of the code and the correct Word Chart is now displayed.

    As you know, Word Chart automatically creates its own Excel worksheet and populates it with test data. There appears to be some problem in accessing this worksheet. 

    Best regards.

    Robbie


    RERThird

    Tuesday, August 18, 2015 4:04 PM
  • <<As you know, Word Chart automatically creates its own Excel worksheet and populates it with test data. There appears to be some problem in accessing this worksheet. >>

    The next step, logically, is to remove the comments "line-by-line" until you hit the one that's causing a problem.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, August 18, 2015 5:15 PM
    Moderator
  • Hi Cindy and Fei,

    Thank you again.

    I added Dim c as Word.chart

    and put the Cells values in Quotes (as I think is required if the value type is set to the default "text".

    The code hasn't been extensively tested, but the sample appears to be working properly. The next step is to delete the last two columns, clear the remaining first two columns and add the data that are obtained from a SQL Server database. I know how to do this, but I have had past timing problems when programmatically adding data to an Excel sheet.

    Sub TestGraph_Click()


    'On Error GoTo ErrorHandler


    10 Dim WordDoc As Word.Document
    20 Dim WordApp As Word.Application
    30    Set WordApp = CreateObject("Word.Application")
    40    With WordApp
    50        .Visible = True
    60        .WindowState = wdWindowStateMaximize
    70        .Documents.Add
    90    End With
        
    100 Set WordDoc = WordApp.ActiveDocument
        WordDoc.Activate
         
    110 Dim ils As InlineShape
    120 Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
    130 With WordDoc.InlineShapes(1).Chart.ChartData
    140  .Activate
    150 End With

    152 WordDoc.InlineShapes(1).Chart.HasTitle = True
    154 WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "This is a new test"

    Dim c As Word.Chart
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim lo As Excel.ListObject

    160    Set c = ils.Chart
    170    Set wb = c.ChartData.Workbook
    180    Set ws = wb.Worksheets(1)
    190    Set lo = ws.ListObjects(1)
    200    lo.Resize wb.Application.Range("A1:D7")
    210    With ws
    220      .Cells(6, 1).Value = "6"
    230      .Cells(6, 2).Value = "6.8"
    240    End With

    'ErrorHandler:
    'msgBoxReturn = MsgBox("Error Source: Excel Sheet/Chart" & vbCrLf & "Error Line: " & Erl & vbCrLf & "Error Number: " & Err.Number & vbCrLf & Err.Description, vbCritical, "")
    'Err.Clear
    'objExcel.Quit.Quit SaveChanges:=wdDoNotSaveChanges

    Best regards,

    Robbie


    RERThird

    Tuesday, August 18, 2015 7:03 PM
  • Thanks to the excellent assistance of Cindy and Fei the VBA program to create and display a Microsoft Word Chart finally works.

    This program uses the 64 bit versions of Microsoft Office 2013 ( Access, Word and Excel).

    The VBA code is as follows:

    Sub TestGraph_Click()


    10 Dim WordDoc As Word.Document
    20 Dim WordApp As Word.Application
    30    Set WordApp = CreateObject("Word.Application")
    40    With WordApp
    50        .Visible = True
    60        .WindowState = wdWindowStateMaximize
    70        .Documents.Add
    90    End With
        
    100 Set WordDoc = WordApp.ActiveDocument
        WordDoc.Activate
         
    110 Dim ils As InlineShape
    120 Set ils = WordDoc.InlineShapes.AddChart(Type:=xlLineMarkers)
    130 With WordDoc.InlineShapes(1).Chart.ChartData
    140  .Activate
    150 End With

    152 WordDoc.InlineShapes(1).Chart.HasTitle = True
    154 WordDoc.InlineShapes(1).Chart.ChartTitle.Text = "Glucose"

    Dim c As Word.Chart
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim lo As Excel.ListObject

    160    Set c = ils.Chart
    170    Set wb = c.ChartData.Workbook
    180    Set ws = wb.Worksheets(1)
    190    Set lo = ws.ListObjects(1)
    200    lo.Resize wb.Application.Range("A1:B6")
        
     With ws
                .Columns("D").Delete
                .Columns("C").Delete
                .Columns("B").Clear
                .Columns("A").Clear

                .Cells(1, 1) = "Date"
                .Cells(1, 2) = "Glucose"
                .Cells(2, 1) = "2/3/2015"
                .Cells(2, 2) = "105"
                .Cells(3, 1) = "3/3/2015"
                .Cells(3, 2) = "90"
                .Cells(4, 1) = "4/3/2015"
                .Cells(4, 2) = "75"
                .Cells(5, 1) = "5/3/2015"
                .Cells(5, 2) = "95"
                .Cells(6, 1) = "6/3/2015"
                .Cells(6, 2) = "102"
     
     End With
     
     WordApp.WindowState = wdWindowStateMinimize
     WordApp.WindowState = wdWindowStateMaximize
        
    End Sub

    Robert Robinson


    RERThird

    • Marked as answer by RERThird Wednesday, August 19, 2015 1:57 AM
    Wednesday, August 19, 2015 1:57 AM