word VBA and line or Radar graph

DevWord · #1 12-08-2016, 12:38 AM

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 · #2 12-08-2016, 04:04 AM

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

Code:

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 · #3 12-08-2016, 08:29 AM

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 · #4 12-08-2016, 02:58 PM

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 · #5 12-08-2016, 10:31 PM

Quote:

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

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 · #6 12-08-2016, 11:04 PM

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.

Code:

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 · #7 12-08-2016, 11:29 PM

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 · #8 12-08-2016, 11:33 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do you paste a graph from Excel into Word as picture without line appearing?	swygant	Word	10	07-01-2014 03:23 PM
Custom Line Graph x, y1, y2	bendi	Excel	2	06-26-2014 07:07 AM
column clustered bar graph with a line graph as secondary axis	Alaska1	Excel	2	06-09-2014 07:13 PM
Priority of line graph vs bar graph in terms of front to back positioning on a chart	DavePPointuser	PowerPoint	1	04-30-2014 10:57 AM
Line Graph Overlay	mgonda	Office	0	11-14-2011 05:53 PM