[SOLVED] Create Excel chart from specified range

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