[excel] aggiornamento automatico conteggi su file generato al volo

[john_revelator]

Ciao a tutti. Spiego velocemente il mio problema.
Ho un file di excel che viene creato al volo con vba da una query di access.

Dopo aver riportato nel foglio tutto il recordset di access, accodo ai record alcuni campi calcolati riepilogativi utilizzando la funzione conta.se in questo modo

valore1 = xlApp.CountIf(xlSheet.Range(intervallo), "valore1")
valore2 = xlApp.CountIf(xlSheet.Range(intervallo), "valore2")

Ovviamente se elimino ad esempio una riga nel foglio creato, la funzione conta.se non si aggiorna automaticamente perchè il valore è già stato calcolato.
Mi chiedevo quindi se esistesse un modo per ovviare a questo problema.

[MarcoGG]

valore1 = xlApp.CountIf(xlSheet.Range(intervallo), "valore1")
valore2 = xlApp.CountIf(xlSheet.Range(intervallo), "valore2")

Ovviamente se elimino ad esempio una riga nel foglio creato, la funzione conta.se non si aggiorna automaticamente perchè il valore è già stato calcolato.
Mi chiedevo quindi se esistesse un modo per ovviare a questo problema.


In questo modo fai calcolare il tutto una volta sola, durante la creazione VBA del nuovo Sheet Excel, facendo eseguire ad Access una macro di Excel... Un po' tortuoso... E ovviamente nel Foglio ti ritrovi il risultato della Fx, e non la funzione stessa.

Io farei così :

1. Creo una variabile prima dell'esportazione del recordset, che contiene il RecordCount. Questo serve per "costruire" la seguente funzione.
Poniamo che questa variabile si chiami "NUM".

2. Sul foglio appena creato fai inserire da Access la formula in questo modo :

Range("A" & (NUM + 1)).FormulaR1C1 = "=COUNTIF(R[-" & NUM & "]C:R[-1]C,""11"")"

[john_revelator]

Ti ringrazio, sei stato molto gentile (speravo proprio in una tua risposta :) ).
Stasera cercherò di mettere in pratica i tuoi consigli, eventualmente tornerò a chiedere. :)

[MarcoGG]

Ti ringrazio, sei stato molto gentile (speravo proprio in una tua risposta :) ).
Stasera cercherò di mettere in pratica i tuoi consigli, eventualmente tornerò a chiedere. :)


Magari posta uno dei tuoi file-tipo Excel generati da Access... Così faccio prima... ;)

[john_revelator]

Magari posta uno dei tuoi file-tipo Excel generati da Access... Così faccio prima... ;)

Grazie, gentilissimo.
Prima ci sbatto la testa da solo, se avrò problemi saprò di poter contare sul tuo aiuto. :)

[john_revelator]

Purtroppo devo chiedere il tuo aiuto. Mi sto incasinando non poco. :fagiano:
Allora, per il momento sto provando il tutto su una query "statica". Penso che una volta capito il meccanismo riuscirò ad adattarlo alle mie query create con QueryDef.

Supponendo che il mio foglio generato sia così


A B C D
1 QUA ANDRA' IL TITOLO
2 ID NOME COGNOME TIPO
3 ... ... ... lavorativo
4 formativo
5 lavorativo
6
...
17 ... ... ... formativo


quindi con la prima riga che conterrà l'intestazione creata dinamicamente, la seconda riga che conterrà le intestazioni di colonna e i record contenuti nell'intervallo a3:d17, a me interessa che ad esempio appaia nella cella d19 il totale di lavorativo, nella cella d20 il totale di formativo eccetera (sono in tutto quattro tipologie).

Ho usato una variabile per il conteggio dei record in questo modo

Record = objRST.RecordCount

la usavo già prima anche con la mia soluzione casereccia. :D
Stavo provando ad adattare la tua formula alla mia situazione ma invano.

Uno dei vari tentativi è stato questo

lavDin = Range("D" & (Record + 2)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""lavorativo"")"
xlSheet.Cells(Record + 10, 4).Value = lavDin

ma mi restituisce FALSO.

Grazie per la pazienza. :)

[john_revelator]

Ho fatto qualche piccolo progresso.

xlSheet.Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""lavorativo"")"

Se scrivo così nella cella sottostante l'ultimo record, viene visualizzato il numero corretto con la funzione conta.se e anche eliminando le righe il conteggio viene aggiornato nel modo corretto.

Però come detto a me serve applicare la funzione quattro volte.
Se scrivo così ad esempio, estendendo la conta a due tipologie

xlSheet.Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""lavorativo"")"
xlSheet.Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""formativo"")"

la conta di lavorativo non appare ed esce solo quella di formativo.
Avrei anche un'altra domanda. I conteggi devono trovarsi per forza a partire dalla cella immediatamente sottostante l'ultimo record oppure no?

[john_revelator]

Sto ancora provando. Se scrivo

xlSheet.Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""lavorativo"")"
xlSheet.Range("D" & (Record + 3)).Offset(1, 0).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""formativo"")"
xlSheet.Range("D" & (Record + 3)).Offset(2, 0).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""avvio impresa"")"
xlSheet.Range("D" & (Record + 3)).Offset(3, 0).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""altro"")"

i valori appaiono tutti e quattro ma non fanno riferimento allo stesso intervallo (nel mio esempio d3:d17) ma diventano d4:d18, d5:d19 eccetera. Come faccio a bloccare il range su cui deve agire ?

[MarcoGG]

Però come detto a me serve applicare la funzione quattro volte.
Se scrivo così ad esempio, estendendo la conta a due tipologie

xlSheet.Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""lavorativo"")"
xlSheet.Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & Record & "]C:R[-1]C,""formativo"")"

la conta di lavorativo non appare ed esce solo quella di formativo.
Avrei anche un'altra domanda. I conteggi devono trovarsi per forza a partire dalla cella immediatamente sottostante l'ultimo record oppure no?


La tua seconda domanda è chiaramente legata alla prima. :)
Se tu applichi contemporaneamente le due linee di codice che hai postato, la prima formula verrà inserita, e subito sovrascritta dalla seconda.
Puoi mettere il risultato della seconda formula in una cella subito sotto, semplicemente incrementando di 1 quel "(Record+3)".
Inoltre c'è un altro errore nel tuo codice, perchè se "stacchi" di 3 celle rispetto all'ultima che contiene un record, il TUTTO sarà :

Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & (Record + 3 - 1) & "]C:R[-3]C,""11"")"

Range("D" & (Record + 4)).FormulaR1C1 = "=COUNTIF(R[-" & (Record + 4 - 1) & "]C:R[-4]C,""11"")"

E così via...
Prova... ;)

[MarcoGG]

Ho già risposto anche al tuo ultimo post... ;)

[MarcoGG]

Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & (Record + 3 - 1) & "]C:R[-3]C,""11"")"

Range("D" & (Record + 4)).FormulaR1C1 = "=COUNTIF(R[-" & (Record + 4 - 1) & "]C:R[-4]C,""11"")"


Scusa, c'è un "11" che non c'entra un fico...:D
L' avevo usato io per fare un test... Mettici il TUO valore ovviamente... :

Range("D" & (Record + 3)).FormulaR1C1 = "=COUNTIF(R[-" & (Record + 3 - 1) & "]C:R[-3]C,""TUO_VALORE"")"

Range("D" & (Record + 4)).FormulaR1C1 = "=COUNTIF(R[-" & (Record + 4 - 1) & "]C:R[-4]C,""TUO_VALORE"")"

[john_revelator]

Sei un grande. Adesso funziona tutto. Grazie mille :)

Ho un'ultimissima domanda e poi non ti stresso più :D .
Mi servirebbe sotto i 4 valori che anche il totale venisse generato automaticamente.

Sto facendo un pò di prove ma sicuramente sbaglio la sintassi.

[john_revelator]

Così funziona

xlSheet.Range("D" & (Record + 7)).Formula = "=SUM(D" & Record + 3 & ":D" & Record + 6 & ")"

Mi hai risolto un grossissimo problema.
Grazie ancora di tutto e complimenti. :)

[MarcoGG]

:cool: