Have MS Access Query populate the sheet of a report embedded MS Excel Chart in Access 2013

Question

I am trying to take advantage of the nicer plotting features of MS Excel, in a custom report in Access 2013. I have a query output that I just need to direct to the sheet portion of the MS Excel Chart Object. I am pretty decent at VBA, but I am not sure how begin on this.

UPDATE

Here Is what I have so far which is virtually nothing.

On the form, I do not see an onload event so I am adding this VBA code under onPaint. I have a summary query which has all my data. My intent is to loop through the query results and place it in a given cell based on the output.

    Dim query As QueryDef
    Dim data As Recordset

    Set data = query.OpenRecordset("Library Qualificatoin Summary")

    If data.RecordCount <> 0 Then
         Me.dataSummaryChart.***STUCK HERE*** 


    end if

@Sam I posted what I have so far which is not much. I am not sure how I get to the sheet part of the unbound MS Excel Chart object — TheCodeNovice, Apr 30 '15 at 14:03
"pretty decent at vba" ;) If data.RecordCount <> 0 will raise error if you don't have a null result set :) — Krish, Apr 30 '15 at 14:08
@krish Decent being a relative term. I am more comfortable with Excel than Access. I was going for a check to see if the query has data.In any event do you know how to get to the worksheet on a MS Excel Chart Object? — TheCodeNovice, Apr 30 '15 at 14:19
What is the type of chart object you use? Having a brain dead moment and couldnt find much when googling. Have you tried doing "me.DataSummaryChart.SourceData Source:=data. Since I cannot find the object I cannot actually check if that is a member or anything but might help for a start — Sam, Apr 30 '15 at 14:21
You need to use Excel.Application, load your template work document where it has a data range and its chart. push the result set to that range and let the excel update the chart automatically. that would be one way. — Krish, Apr 30 '15 at 14:22
@Sam I am using the MS Excel Chart Object. The issue is it is impossible to find any documentation on manipulating the object in an MS Access form — TheCodeNovice, Apr 30 '15 at 16:24
That would be why I couldn't find anything. I will keep trying though to find anything — Sam, Apr 30 '15 at 17:21
@krish Hey, do you have any experiences manipulating an embedded spreadsheet object in an access form? That would work for me. I just cannot seem to drill down past this frame layer to get to the excel object level. — TheCodeNovice, Apr 30 '15 at 19:48
first of all why do you want to use excel chart if Access has one? if you want to use Excel why do you want to embed the excel sheet in access form? Are you trying to create new excel document and show the chart? or are you trying to embed an excel object in access. please clarify your question.. — Krish, Apr 30 '15 at 20:18
@krish I had a workbook in excel that became too slow due to the amount of data and so I am porting everything to Access to make the large amount of data more manageable. I find Access's native charts to clunky and I wanted to have excel show my data but have Access as my back-end. — TheCodeNovice, Apr 30 '15 at 20:27

score 0 · Answer 1 · edited May 23 '17 at 10:29

The best answer I have found so far is to embed a worksheet object rather than a chart following the solution posted in another exchange post. link

my code so far (still updating to final version)

Dim chartsheet As Excel.Worksheet
Dim query As QueryDef
Dim data As Recordset


Set chartsheet = Me.summaryData.Object.Sheets(1)
chartsheet.Cells(1, 1).Value = 2

From here I am going to just manually place the data into excel and as Krish mentioned have the chart auto update after you change values . Not the prettiest solution. Hopefully someone comes up with a slicker method.

Have MS Access Query populate the sheet of a report embedded MS Excel Chart in Access 2013

1 Answers1