I have a query in MSAccess 2010 that creates 2 columns of data column 1 is the Date (Month/Yr) and column 2 is a currency value for that date. I can change the query using user input to get different date ranges and currency values. I want to create an Excel graph using VBA commands so I can see the Excel graph in an unbound object frame in MS Access. I am having a hard time creating the Excel objects in VBA to create these charts on the "fly", Any help would be appreciated.
Code:
Dim XL As Excel.Application 'Object ' Excel application
' Dim XLBook As Excel.Workbook ' Excel workbook
' Dim XLSheets As Excel.Sheets ' Excel Worksheet
' Dim XLChart As Excel.Chart ' Excel Chart
' Dim XLSheet As Object
' Dim iRow As Integer ' Index variable for the current Row
' Dim iCol As Integer ' Index variable for the current Row
' Dim rst As ADODB.Recordset
' Dim strQry As String
' Dim strType As String
' Dim strColName As String
' Dim intYr As Integer
' Dim intNumCols As Integer
' Const cNumRows = 1 ' Number of Series
' 'variables defined need to make user chosen
' strType = "DIVCY"
' strColName = "CEF" 'Other choice is Bank codes CEF is the UUA acct
' intYr = 2014
'
' strQry = "SELECT tblIncome.DateStart, tblIncome.CEFAmt" & _
' " FROM tblIncomeType INNER JOIN tblIncome ON tblIncomeType.IncomeSourceID = tblIncome.IncomeType" & _
' " WHERE (((tblIncome.IncomeType) = '" & strType & "') And " & _
' " ((tblIncomeType.CollectionName) = '" & strColName & "') And " & _
' " ((Year([DateStart])) = " & intYr & "))" & _
' " ORDER BY tblIncome.DateStart;"
'Debug.Print strQry
'
' Set rst = New ADODB.Recordset
' With rst
' .ActiveConnection = CurrentProject.Connection
' .CursorLocation = adUseClient
' .CursorType = adOpenDynamic
' .LockType = adLockOptimistic
' .Open strQry, Options:=adCmdText
' End With
'
' '
' intNumCols = rst.RecordCount ' Number of points in each Series
'
'
'
' ReDim aTemp(1 To cNumRows, 1 To intNumCols)
'
' 'Start Excel and create a new workbook
' Set XL = New Excel.Application '("Excel.application")
%%%%%%%%%%% Error Occurs here at XL.Charts says Method Charts failed.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
' Set XLChart = XL.Charts.Add
'
' Set XLBook = XL.Workbooks.Add
' Set XLSheets = XLBook.Sheets
' Set XLSheet = XLSheets.Item("NewSheet")
'' Set XLSheet = XL.Worksheets.Item(1)
' 'XLSheet.Add
' ' Insert Random data into Cells for the two Series:This is where you put the data from the qry
' 'need way to get data from qry
' rst.MoveFirst
' For iRow = 1 To cNumRows
' For iCol = 1 To intNumCols
' aTemp(iRow, iCol) = rst!CEFAmt
' rst.MoveNext
' Next iCol
' Next iRow
'
' Debug.Print XLBook.Sheets.Count
' XLSheet = XLSheet.sItem("NewSheet")
' XLSheet.Range("A1").Resize(cNumRows, intNumCols).Value = aTemp
'
'
' 'Add a chart object to the first worksheet
'
' Set XLChart = XLSheet.ChartObjects.Add(50, 40, 300, 200).Chart
' XLChart.SetSourceData Source:=XLSheet.Range("A1").Resize(cNumRows, intNumCols)
'
' ' Make Excel Visible:
' XL.Visible = True
'
' XL.UserControl = True
'
' 'set series 1 to "Dividends CY"
'
' XLChart.HasTitle = True
' 'Debug.Print oChart.
' 'oChart.ChartTitle = "Dividends for " & CStr(intYr)