word report from access

[DevAccess]

Hello

I would like to generate a chart on word document from access, following is mine environement.

OS : windows 10/64 bit
ms office 2010/32 bit

Now following is code on my access form button just for testing I am playing with the code and seeing how it would looks like when actual application is being implemented.

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

I have referenced microsoft word 14.0 object library into access reference.

But when I generate this generates the line/series chart instead of radar chart from access, can anybody please help me why this is happening instead of I have provided to VBA to do radar chart. Also, this put series 3 in line graph which is no where mentioned in VBA code.

Please let me know what I have to do generate Radar chart in this VBA programme.

Also, if I do same report in ms word macro it is doing what was expected I mean it generates radar chart.

 

[sneuberg]

When I tried this it doesn't compile so I removed Option Explicit from the module and ran the code and it produced a bar chart. Putting Option Explicit back I fixed the code by adding these declarations

Dim wdapp As Word.Application
Dim wddoc As Word.Document

it still didn't compile complaining about xlRadarMarkers not being define. I saw that this was a Excel constant so I added the Microsoft Excel Library reference. The code compiled and when I ran it it produced a radar chart. I have Windows 7, Excel 2013 and Access 2013.

I strongly encourage you to add Option Explicit for your modules. You can have have it added for you by checking Require Variable Declaration in Tools, Option, Editor Tab. This will force you to declare you variables but the little extra effort can save you a lot of headaches.

 

[DevAccess]

When I tried this it doesn't compile so I removed Option Explicit from the module and ran the code and it produced a bar chart. Putting Option Explicit back I fixed the code by adding these declarations

Dim wdapp As Word.Application
Dim wddoc As Word.Document

it still didn't compile complaining about xlRadarMarkers not being define. I saw that this was a Excel constant so I added the Microsoft Excel Library reference. The code compiled and when I ran it it produced a radar chart. I have Windows 7, Excel 2013 and Access 2013.

I strongly encourage you to add Option Explicit for your modules. You can have have it added for you by checking Require Variable Declaration in Tools, Option, Editor Tab. This will force you to declare you variables but the little extra effort can save you a lot of headaches.

Thanks a ton sneburg, it worked !!!!

 

[DevAccess]

Thanks a ton sneburg, it worked !!!!

Hi Sneburg,

Can you please assist me in below thread as well ?

http://www.access-programmers.co.uk/forums/showthread.php?t=290924