#1
|
|||
|
|||
word VBA and line or Radar graph
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 ? |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Quote:
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 |
#6
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |