Update graph's usedrange, VBA

[Lifeseeker]

Hi there,

I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.

what I am trying to do:

1) click on the button
2) button will update the usedrange in "trending" tab
3) have the graph's data range based on the usedrange.

This is the code

Dim xlApp As Object
       
        Set xlApp = CreateObject("Excel.Application")
       
        xlApp.Visible = True
        xlApp.Workbooks.Open "C:\Documents and Settings\gjia\Desktop\metric_DEV.xlsx", True, False
  
  xlApp.worksheets("Trending").Activate
    
    'Dim CHARTDATA As Range
    'Set CHARTDATA = Range(Range("A1"), Range("A1").Offset(1, 0).End(xlToRight))
    'ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=CHARTDATA

    
      With xlApp.ActiveSheet
            .ChartObjects("Chart 1").Activate
            .SetSourceData Source:=xlApp.worksheets("Trending").usedrange
        End With

note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...

Can someone help me?

Thanks

 

[Lifeseeker]

Hi there,

I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.

what I am trying to do:

1) click on the button
2) button will update the usedrange in "trending" tab
3) have the graph's data range based on the usedrange.

This is the code

Dim xlApp As Object
       
        Set xlApp = CreateObject("Excel.Application")
       
        xlApp.Visible = True
        xlApp.Workbooks.Open "C:\Documents and Settings\gjia\Desktop\metric_DEV.xlsx", True, False
  
  xlApp.worksheets("Trending").Activate
    
    'Dim CHARTDATA As Range
    'Set CHARTDATA = Range(Range("A1"), Range("A1").Offset(1, 0).End(xlToRight))
    'ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=CHARTDATA

    
      With xlApp.ActiveSheet
            .ChartObjects("Chart 1").Activate
            .SetSourceData Source:=xlApp.worksheets("Trending").usedrange
        End With

note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...

Can someone help me?

Thanks

Can someone help me look into this?

 

[JHB]

Try the below code:

 Dim xlApp As Object, myRange
  
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = True
  xlApp.Workbooks.Open "C:\Documents and Settings\gjia\Desktop\metric_DEV.xlsx", True, False   
  xlApp.worksheets("Trending").Activate
  xlApp.Application.ActiveSheet.UsedRange.Select
  Set myRange = xlApp.ActiveCell.CurrentRegion
  xlApp.ActiveSheet.ChartObjects("Chart 1").Activate
  xlApp.ActiveChart.SetSourceData Source:=myRange

 

[Lifeseeker]

Try the below code:

 Dim xlApp As Object, myRange
  
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = True
  xlApp.Workbooks.Open "C:\Documents and Settings\gjia\Desktop\metric_DEV.xlsx", True, False   
  xlApp.worksheets("Trending").Activate
  xlApp.Application.ActiveSheet.UsedRange.Select
  Set myRange = xlApp.ActiveCell.CurrentRegion
  xlApp.ActiveSheet.ChartObjects("Chart 1").Activate
  xlApp.ActiveChart.SetSourceData Source:=myRange

Thank you. the requirement has just gotten changed, but this is working. Thank you.