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
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