hi, i took another approach.
this is the button on the form i use to open the form with objectframe
Code:
Private Sub MappaBTN_Click() Dim xlx As Object, xlw As Object, Xls As Object, xlc As Object
Dim db As DAO.Database, rst As DAO.Recordset
Dim Worksheetname As String
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM OcfXRegione")
DoCmd.RunSQL ("INSERT INTO OcfXRegione (Regione, Conteggio) SELECT Regione,Conteggio FROM qryOcfXregione")
Set db = CurrentDb
On Error Resume Next
Set xlx = GetObject(, "Excel.application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.application")
End If
Err.Clear
On Error GoTo 0
xlx.Visible = False ' se setto vero mi apre il file excel in questione
Set rst = db.OpenRecordset("OcfXregione")
If rst.RecordCount > 0 Then
Set xlw = xlx.Workbooks.Open("C:\Users\diegomarino\OneDrive - Indipendente\Lavoro\Informatica\Gestionale\FrontEnd\Grafici\CFNumeroMappa.xlsx")
Set Xls = xlw.Worksheets("qryOcfXRegioni")
Set xlc = Xls.Range("A2")
Do While Not rst.EOF Or rst.BOF
xlc = rst.Fields(0).Value
xlc.Offset(0, 1).Value = rst.Fields(1).Value
Set xlc = xlc.Offset(1, 0)
Debug.Print (xlc)
rst.MoveNext
Loop
xlx.DisplayAlerts = False
'xlw.RefreshAll
' With xlw.grafico1
' .SetSourceData Source:=Sheets("qryOCFXRegione").Range("$A$1:$b$21")
' 'other chart formatting code that all works fine
' End With
DoEvents
xlw.Save
xlw.Close SaveChanges:=True
xlx.DisplayAlerts = True
End If
rst.Close
Set rst = Nothing
Set xlc = Nothing
Set Xls = Nothing
Set xlw = Nothing
xlx.Quit ' se esiste l'applicazione excel la chiude
Set xlx = Nothing
DoCmd.OpenForm "AnalisiOCFxregione"
DoCmd.SetWarnings True
then, i have a combobox in which i can filter data (that's very simple data, just 20 rows max with two columns)
Code:
Private Sub CercaBancaCB_AfterUpdate()
Dim xlx As Object, xlw As Object, Xls As Object, xlc As Object, oChart As Object
Dim db As DAO.Database, rst As DAO.Recordset
Dim Worksheetname, WorkBookName As String
Dim i As Integer
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM ocfXregione")
DoCmd.RunSQL ("INSERT INTO OcfXRegione (REgione, conteggio) " & vbCrLf & _
"SELECT Regione, conteggio " & vbCrLf & _
"FROM( " & vbCrLf & _
"SELECT Comuni.Regione, Count(IscrittiOCF.regione) AS Conteggio " & vbCrLf & _
"FROM Comuni INNER JOIN (OCFComuni INNER JOIN IscrittiOCF ON OCFComuni.COMUNE = IscrittiOCF.COMUNE) ON Comuni.IDComune = OCFComuni.ComuneID " & vbCrLf & _
"WHERE (iscrittiocf.regione <> """" AND iscrittiocf.regione <> ""\n"") AND (IscrittiOCF.DEnominazione_soggetto_abilitato = FORMS!Analisiocfxregione!Cercabancacb or FORMS!Analisiocfxregione!Cercabancacb is null) " & vbCrLf & _
"GROUP BY Comuni.Regione)")
Me.Requery
Set db = CurrentDb
On Error Resume Next
Set xlx = GetObject(, "Excel.application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.application")
End If
Err.Clear
On Error GoTo 0
xlx.Visible = False ' se setto vero mi apre il file excel in questione
Set rst = db.OpenRecordset("OcfXregione")
If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst
WorkBookName = "C:\Users\diegomarino\OneDrive - Indipendente\Lavoro\Informatica\Gestionale\FrontEnd\Grafici\CFNumeroMappa.xlsx"
Set xlw = xlx.Workbooks.Open(WorkBookName)
xlw.RefreshAll
Set Xls = xlw.Worksheets("qryOCFXRegioni")
Set xlc = Xls.Range("A2")
Do While Not rst.EOF Or rst.BOF
xlc = rst.Fields(0).Value
xlc.Offset(0, 1).Value = rst.Fields(1).Value
Set xlc = xlc.Offset(1, 0)
Debug.Print (xlc)
rst.MoveNext
Loop
Set xlc = Xls.Range("A" & rst.RecordCount + 2)
i = rst.RecordCount + 2
Do Until i = 22
xlc = ""
xlc.Offset(0, 1).Value = ""
Set xlc = xlc.Offset(1, 0)
i = i + 1
Loop
xlx.DisplayAlerts = False
xlw.Close SaveChanges:=True
xlx.DisplayAlerts = True
End If
rst.Close
Set rst = Nothing
Set xlc = Nothing
Set Xls = Nothing
Set xlw = Nothing
xlx.Quit ' se esiste l'applicazione excel la chiude
Set xlx = Nothing
DoCmd.SetWarnings True
End Sub
everything work perfectly, except the chart sometimes is not refreshing in objectframe, and when i open the excel it tooks few seconds to show the chart even in excel. Why that?
i'd liek to stick with this method that's the best i found