excelvba

Create Excel chart from specified range


I have an Access database that calls an Excel macro, which in turn runs the below Excel macro called CreateGraph.

CreateGraph is supposed to take data from range A1:D12 from a sheet labeled 'Summary'.

I get

subscript out of range

I have references to 'Microsoft Office 16.0 object library' and also 'Microsoft Excel 16.0 ojbect library'.

Sub CreateGraph()
    
    Dim xlApp As Object 'Excel.Application
    Dim xlWB As Object  'Excel.Workbook
    Dim xlSh As Object  'Excel.Worksheet
    
    Set xlApp = CreateObject("Excel.Application")   'New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("X:\path_here " & Format(Date, "mm-DD-yy") & ".xlsx")
    Set xlSh = xlWB.Sheets("Summary")
    
    xlApp.Visible = True
    'code to format the excel file BEGINS
    
    Set xlWB = ActiveWorkbook
    
    Sheets("Summary").Columns("A:D").Select
    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    ActiveChart.SetSourceData Source:=Range("Summary!$A:$D")
    
    ActiveWorkbook.Save
    
End Sub

Solution

  • First, since you're using late binding, there's no need to set a reference to the Excel object library. Secondly, your macro can be re-written as follows...

    Sub CreateGraph()
        
        Dim xlApp As Object
        Dim xlWB As Object
        Dim xlSh As Object
        Dim xlShp As Object
        Dim xlChrt As Object
        
        Set xlApp = CreateObject("Excel.Application")   'New Excel.Application
        xlApp.Visible = True
        
        Set xlWB = xlApp.Workbooks.Open("X:\path_here " & Format(Date, "mm-DD-yy") & ".xlsx")
        
        Set xlSh = xlWB.Sheets("Summary")
        
        Set xlShp = xlSh.Shapes.AddChart2(297, 52) '52=xlColumnStacked
        
        Set xlChrt = xlShp.Chart
        
        xlChrt.SetSourceData Source:=xlSh.Range("$A:$D")
        
        xlWB.Save
        
    End Sub