Excel Chart from recording macro not working

Question

I have an Access database that calls an Excel macro, which in turn opens and runs the below Excel macro called 'createGraph'. The Excel macro is supposed to take data from range A1:D12 from a sheet labeled 'Summary'.

I get a 'subscript out of range' error. I have references to 'Microsoft Office 16.0 object library' and also 'Microsoft Excel 16.0 ojbect library'. Please help. Below is the code snippet. Any help will be greatly appreciated:

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

Answer 1

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