Hiding excel when controlling charts in word

rob81

New Member
Joined
Jan 18, 2011
Messages
1
Hi,

I'm creating a dynamic graph (embeded excel workbook - Chartdata) in word where the user changes values in textboxes, these help to plot a graph.

3 questions

1. I can't keep the excel sheet hidden when I replot the graph.

I've tried

salesChart.ChartData.Workbook.Application.visible = false

with no sucess.

2. How can I add in screenupdating = false to the word doc?
Have tried application.screenupdating and document.screenupdating with no success.


3. You will notice that the graph is deleted and re created each time.
How and I amend the existing chartdata sheet?

thanks in advance for any help,

Rob


Code:
Sub delete()

Selection.GoTo what:=wdGoToTable, which:=wdGoToFirst, Count:=1, Name:=""

InlineShapes(11).delete

    D = Drug_costs.Value

    H = Hospital_costs.Value

    V = Vehicle_cost.Value

    Dim oneSeries As Boolean
    oneSeries = True

    
    Dim salesChart As Chart
    Dim chartWorkSheet As Excel.Worksheet
    
    ' Add in a new chart
    Set salesChart = ActiveDocument.InlineShapes.AddChart.Chart

    salesChart.ChartType = xlXYScatterLinesNoMarkers
        
    Set chartWorkSheet = salesChart.ChartData.Workbook.Worksheets(1)
    
    ' Resize the chart area
    chartWorkSheet.ListObjects("Table1").Resize     
chartWorkSheet.Range("A1:B152")
    
    
    ' Add data to the chart
    
    For i = 0 To 151
    
    chartWorkSheet.Cells(i + 2, 1) = i

    chartWorkSheet.Cells(i + 2, 2) = (H * i) - (D * i) - V

    Next i
    
    
    ' Quit Excel, since we no longer need it
    salesChart.ChartData.Workbook.Application.Quit

End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top