word VBA and line or Radar graph

[DevWord]

Hello there,



Can I plot the Radar or line graph using word VBA ? or if I take the access data as reference can I use access VBA to generate line or radar graph using access data as input parameter ?

if so Can you please explain me how this can be done ?

[macropod]

You can do that kind of thing using code such as:

Sub Demo()
Dim iShp As InlineShape, wb As Object, ws As Object
With ActiveDocument
  Set iShp = .InlineShapes.AddChart(Type:=xlRadarFilled, Range:=.Range.Characters.Last)
  With iShp
    Set wb = .Chart.ChartData.Workbook
    wb.Application.ScreenUpdating = False
    Set ws = wb.Worksheets(1)
    With ws
      .Range("A2:A7").NumberFormat = "General"
      .Range("A2").Value = 1
      .Range("A3").Value = 2
      .Range("A4").Value = 3
      .Range("A5").Value = 4
      .Range("A6").Value = 5
      .Range("A7").Value = 6
      .Range("B1").Value = "Item 1"
      .Range("B2").Value = 100
      .Range("B3").Value = 120
      .Range("B4").Value = 140
      .Range("B5").Value = 160
      .Range("B6").Value = 180
      .Range("B7").Value = 200
      .Range("C1").Value = "Item 2"
      .Range("C2").Value = 210
      .Range("C3").Value = 190
      .Range("C4").Value = 170
      .Range("C5").Value = 150
      .Range("C6").Value = 130
      .Range("C7").Value = 110
    End With
    wb.Application.Quit
    .Chart.Refresh
  End With
End With
End Sub

[DevWord]

thanks Macropod for your help, my query is that it opens up excel sheet in back-end , is it compulsory to have excel as backend data what if want to data from access databases ?

THanks

[macropod]

That behaviour is unavoidable. The graph exists in an embedded Excel object and depends on data that are held in the worksheet behind it. The fact the data are sourced from Access is immaterial.

[DevWord]

Originally Posted by macropod

That behaviour is unavoidable. The graph exists in an embedded Excel object and depends on data that are held in the worksheet behind it. The fact the data are sourced from Access is immaterial.

Hi Macropod, ok I understood, now samething I am doing with MS access just to see how this goes with ms access, I created a form and that with a button and code given by you as backend code for the button.

I did not do any code changes at all, well it generates the graph but for that generates series chart with series 3 as an addition item 1 and item 2 which have it VBA code.

Please find attached snapshot, also in ms access i have added Microsoft word 14.0 object library as add in reference.

I have MS office 2010/32 bit/ windows 10 running on my system.

below is code

Private Sub Command0_Click()

Dim iShp As InlineShape, wb As Object, ws As Object
 Set wdapp = CreateObject("Word.Application")
    wdapp.Visible = True
    Set wddoc = wdapp.Documents.Add
With wddoc 'xlLineMarkers,xlRadarFilled
  Set iShp = .InlineShapes.AddChart(Type:=xlRadarMarkers, Range:=.Range.Characters.Last)
  With iShp
    Set wb = .Chart.ChartData.Workbook
    Set ws = wb.Worksheets(1)
    With ws
      .Range("A2:A7").NumberFormat = "General"
      .Range("A2").Value = 1
      .Range("A3").Value = 2
      .Range("A4").Value = 3
      .Range("A5").Value = 4
      .Range("A6").Value = 5
      .Range("A7").Value = 6
      .Range("B1").Value = "Item 1"
      .Range("B2").Value = 100
      .Range("B3").Value = 120
      .Range("B4").Value = 140
      .Range("B5").Value = 160
      .Range("B6").Value = 180
      .Range("B7").Value = 200
      .Range("C1").Value = "Item 2"
      .Range("C2").Value = 210
      .Range("C3").Value = 190
      .Range("C4").Value = 170
      .Range("C5").Value = 150
      .Range("C6").Value = 130
      .Range("C7").Value = 110
    End With
    .Chart.Refresh
   wb.Application.Quit
  End With
End With



End Sub

[macropod]

The following works fine for me. Note that I changed the position of the .Chart.Refresh line, but that's only to get the chart data to refresh correctly.

Sub Demo()
Dim iShp As InlineShape, wb As Object, ws As Object
With ActiveDocument
  Set iShp = .InlineShapes.AddChart(Type:=xlRadarMarkers, Range:=.Range.Characters.Last)
  With iShp
    Set wb = .Chart.ChartData.Workbook
    wb.Application.ScreenUpdating = False
    Set ws = wb.Worksheets(1)
    With ws
      .Range("A2:A7").NumberFormat = "General"
      .Range("A2").Value = 1
      .Range("A3").Value = 2
      .Range("A4").Value = 3
      .Range("A5").Value = 4
      .Range("A6").Value = 5
      .Range("A7").Value = 6
      .Range("B1").Value = "Item 1"
      .Range("B2").Value = 100
      .Range("B3").Value = 120
      .Range("B4").Value = 140
      .Range("B5").Value = 160
      .Range("B6").Value = 180
      .Range("B7").Value = 200
      .Range("C1").Value = "Item 2"
      .Range("C2").Value = 210
      .Range("C3").Value = 190
      .Range("C4").Value = 170
      .Range("C5").Value = 150
      .Range("C6").Value = 130
      .Range("C7").Value = 110
    End With
    .Chart.Refresh
    wb.Application.Quit
  End With
End With
End Sub

[DevWord]

this is working for me in ms word macro but when I place same code in access it does chart series chart instead of radar chart and also adds series 3, please find attache database, also I have added microsoft word 14.0 library to make reference to the word

I am sorry but i cannot attach access database here as it does not allow accdb file type.

[macropod]

Well, the code is for Word, not Access. You have, after all, posted in a Word VBA forum. For Word, the mere fact the data are sourced from Access is immaterial but, if you're running the code from Access, you will need to ensure you're telling it when to use Word methods, etc.; otherwise you may end up calling Access methods, etc. instead. If you want help with Access, you should post in an Access forum.