I would appreciate some help on creating an Excel chart in a MS Access form. The data would change depending on user input. User input would create a set of x,y data where x = dates and y would indicate currency values. I have tried some code but I get an error. See code below:
Code:
Dim XL As Excel.Application 'Object ' Excel application
Dim XLBook As Excel.Workbook ' Excel workbook
Dim XLSheet As Excel.Sheets ' Excel Worksheet
Dim XLChart As Excel.Chart ' Excel Chart
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
'Get the data from the tblIncome using strQry
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strQry, Options:=adCmdText
End With
'Get the number of columns for graph
intNumCols = rst.RecordCount ' Number of points in each Series
'set data into aTemp array
ReDim aTemp(1 To cNumRows, 1 To intNumCols)
'Start Excel and create a new workbook
Set XL = New Excel.Application '("Excel.application")
Set XLBook = XL.Workbooks.Add
Set XLSheet = XLBook.Worksheets.Add
'Fill the array with data for row and column
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
'THIS LINE IS WHERE ERROR OCCURS
XLSheet.Range("A1").Resize(cNumRows, intNumCols).Value = aTemp
'METHOD OR DATA MEMBER NOT FOUND on .Range
'Add a chart object to the first worksheet
Set Chart = XLSheet.ChartObjects.Add(50, 40, 300, 200).Chart
Chart.SetSourceData Source:=XLSheet.Range("A1").Resize(cNumRows, intNumCols)
' Make Excel Visible:
XL.Visible = True
XL.UserControl = True
'set series 1 to "Dividends CY"
oChart.HasTitle = True
'Debug.Print oChart.
'oChart.ChartTitle = "Dividends for " & CStr(intYr)
Any help would be appreciated. Thanks in advance