Link Query to OLE Unbound Excel Chart

[Phorkyas]

Greetings Tekies

I have jet another question.
Im trying to viualise my data in a graph which has to be dynamical in several parameters. The standart Access graph is not my favorite option because of 4000 data-point limitation and restricted adjustment obtions.

I tried to go for an Excel graph via a OLE Unbound object.
But i have not much experiance with Unbound Objects. Therefore my question might be kind of basic.

In the Unbound Excel graph there are two sheets, one with the graph and one with some sample data. Of course i now need to fill my data to the sheet with the sample data.
Until now i do this row by row via

Dim xlchart as Object

Set xlchart = Me.OLEUnbound99.Object

Do
   xlchart.Sheets(2).Celles(2+i,1)Value='Some value'
   i=i+1
While i<1000

but this is very unsatisfactory since i have to insert 10000+ rows with multiple columns.

What i want to do now is to link my query which has all the data i want to plot to the OLE Excel sheet. This should be done like the Transferspreadsheet method but to the Unbound object, not a new excel workbook.

Is this possible?

I would be glad about any help, suggestion or workaround.

Thanks in advance
Phorkyas

 

[pdldavis]

I'd opt for a workaround. Some things better done in Excel. You can start Excel from Access...

Private Sub Form_Load()

On Error GoTo Err_frmError

Dim stLink As String
Dim ctl As CommandButton

stLink = "E:\BPCT\BPCTCharts.xlsm"

Set ctl = Me!cmdOpen

With ctl
.Visible = True
.HyperlinkAddress = stLink
.Hyperlink.Follow

End With

DoCmd.Close acForm, "frmOpenCharts", acSaveNo


Exit_frmError:

Exit Sub

Err_frmError:


MsgBox "Error Number: " & Err.Number & " " & "Error Description: " & Err.DESCRIPTION

Resume Exit_frmError


End Sub