VBA EXCEL97: How to get the range from a chart into a range object 
Author Message
 VBA EXCEL97: How to get the range from a chart into a range object

Hi all,

I'm trying to optimize a process that makes a presentation in
powerpoint from data that is updated monthly. Data is inserted into
excel from a query updating some 120 pivotables and producing 120
charts.

Excel messes up the data a bit. Often the sourcedata of the chart
stay's correct, but row and colums are switched. I'm trying to build a
macros that corrects this situation. I'm getting stuck with getting
the rangwe from the current chart.

Any help

Cees

Sub CorrigeerGrafieken()
'Gemaakt door Cees Harlaar (Brunel ICT)
'16-9-2002
Dim mySheet As Object
'Dim myChart As Object
Dim myRange As Range
Dim myIndex As Object
Dim ChartItemCount As Integer
Dim iTeller As Integer
Dim strSheet As String
Dim strChart As String
Dim intVolgnummer As Integer
Set dlgSource = ThisWorkbook.DialogSheets("dlgSource")

    For Each mySheet In ActiveWorkbook.Sheets
        Sheets(mySheet.Name).Select
        'iTeller = 0
        If InStr(1, ActiveSheet.Name, "Query D") Then
            ChartItemCount = ActiveSheet.ChartObjects.Count
            'MsgBox ActiveSheet.Name & " " & ChartItemCount
            For iTeller = 1 To ChartItemCount
                ActiveSheet.ChartObjects.Item(iTeller).Select
                Set myRange =
Range(dlgSource.EditBoxes("ChartSource").Text)
                ctiveChart.SetSourceData Source:=Sheets("Query D for
Dubai").Range( _
        myRange), PlotBy:=xlRows

            Next
        End If
    Next

On Error Resume Next
Kill "c:\data\tempdata.txt"
End Sub



Sun, 06 Mar 2005 18:40:01 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Excel97 bug: Range.Character object

2. Excel97 bug: Range.Character object

3. range and range object difficulty

4. Non consecutive ranges and Ranges objects

5. How to get macro to select range for chart - range varies

6. VBA :Selecting ranges that vary, Naming ranges based on Formatting

7. Getting a range from a chart

8. How do I select a constant time range from a variable time range using VBA

9. Q range = excel.intersect(range, range) ?

10. Getting rid of dupes in a range from a VBA macro


 
Powered by phpBB® Forum Software © phpBB Group