Esistono diversi modi per fare in modo che Excel aggiorni automaticamente le connessioni dati e ricalcoli un foglio di lavoro. Questi trucchi sono più importanti con il tipo di dati Stock in Excel per i clienti di Microsoft 365.
Normalmente Excel si aggiornerà automaticamente quando si modifica un valore di cella. Oggigiorno ci sono situazioni in cui le celle cambiano valore ma Excel NON aggiorna il foglio di lavoro. In altre parole, il moderno Excel è cambiato in modi a cui Microsoft non si è ancora completamente adattato.
Idealmente, Excel avrebbe unimpostazione generale per aggiornare il foglio di lavoro ogni “n” secondi o minuti. Allo stato attuale, abbiamo bisogno di soluzioni alternative per farlo accadere.
Ecco alcune situazioni in cui potrebbe essere necessario forzare laggiornamento o il ricalcolo dei dati o prudente.
Tipo di dati di borsa
Con il tipo di dati di borsa la possibilità di aggiornare automaticamente è più importante. Gli utenti vorranno che i loro fogli di lavoro acquisiscano il lat prezzi est automaticamente, cosa che le versioni di anteprima correnti non possono fare. Invece di avere un simpatico “ticker” automatico, dobbiamo fare clic su “Aggiorna” per ottenere i prezzi più recenti.
I tipi di dati Stock e Geo sono bestie curiose. Sono connessioni dati a origini esterne ma NON vengono visualizzate come connessioni dati Excel. Ciò significa che non puoi impostare un aggiornamento automatico dei dati, come faresti con le normali connessioni dati. In effetti, non ci sono controlli esposti per i tipi di dati Stock o Geo.
NOW () e altre funzioni volatili
La funzione NOW () si aggiorna alla data e allora più recenti ogni volta che Excel ricalcola il foglio di lavoro. Ma se non cè niente per farlo accadere, Now () non cambia il valore. È necessario un fattore esterno per aggiornare Excel ora () e il resto del foglio di lavoro. In altre parole, dovresti essere in grado di dare unocchiata a un foglio di lavoro e sapere che è fino al secondo, ma ciò non è possibile con Excel “pronto alluso”.
Le funzioni personalizzate VBA possono anche essere contrassegnate come volatili utilizzando questa riga nel codice della funzione:
Application.Volatile
Questa riga fa eseguire la funzione ogni volta che Excel aggiorna / ricalcola il foglio di lavoro.
Ulteriore attenzione
Forse vuoi che il tuo foglio di lavoro si aggiorni automaticamente per precauzione? Molte vecchie mani di Excel ricordano situazioni in cui Excel non è stato aggiornato correttamente, quindi a loro piace lapproccio “cintura e bretelle” (almeno occasionalmente).
Codice
Il metodo standard di forzatura automatica laggiornamento di Excel è un breve frammento di codice VBA. Ecco cosa usiamo, ci sono molte variazioni sullo stesso tema. Il codice completo si trova in fondo allarticolo.
Ci sono tre funzioni.
RefreshAllDataConn
esegue leffettivo aggiornamento delle connessioni dati (Cartelle di lavoro (ThisWorkbook.Name) .RefreshAll) e abbiamo aggiunto due righe facoltative per visualizzare lultima volta aggiornato sulla barra di stato inferiore.
Se vuoi essere particolarmente attento, aggiungi una riga per forzare esplicitamente il ricalcolo. O ActiveSheet.Calculate o lestremo Application.CalculateFull (questo rallenterebbe un foglio di lavoro di grandi dimensioni, usalo con parsimonia).
AutoRefresh
esegui il sub RefreshAllDataConn ogni minuto o qualsiasi valore impostato su la riga Application.OnTime Now + TimeValue (“00:01:00”), “AutoRefresh”
Workbook_Open
una funzione Excel incorporata che viene eseguita automaticamente allapertura del foglio di lavoro . In questo caso, viene avviato laggiornamento automatico.
Soluzione alternativa per la connessione dati
Lo svantaggio dellapproccio VBA è che è necessario un foglio di lavoro .xlsm (foglio di lavoro Excel abilitato per le macro). Possono esserci problemi di condivisione di file abilitati per macro a causa di problemi di sicurezza.
Larrivo di PowerQuery / Get e Transform significa che cè un altro modo per forzare un ricalcolo del foglio di lavoro. È una soluzione alternativa e non è perfetta, ma è possibile e non necessita di un foglio di lavoro abilitato per le macro.
In breve, assicurati che sia presente una configurazione della query di dati con aggiornamento automatico. Se non è presente una connessione dati, aggiungine una piccola al foglio di lavoro.
Una volta ottenuta una query con aggiornamento automatico, dovrebbe essere aggiornato anche il foglio di lavoro che include tutte le funzioni volatili.
Idealmente, dovrebbero essere aggiornati anche i tipi di dati Stock e Geo.
Qualsiasi query di dati di Excel include alcune opzioni di aggiornamento nelle proprietà della query. La maggior parte di essi è disattivata per impostazione predefinita.
Aggiorna ogni nnn minuti: limpostazione predefinita è disattivata con 60 minuti suggeriti.
Aggiorna i dati allapertura del file
Abilita aggiornamento in background
Aggiorna questa connessione su Aggiorna tutto
La soluzione per laggiornamento automatico consiste nel creare un piccolo e praticamente insignificante connessione dati. Quindi configura la connessione dati per aggiornarla ogni minuto o in qualsiasi momento desideri. Ciò dovrebbe forzare laggiornamento del foglio di lavoro incluse le funzioni volatili sopra menzionate.
Alcune versioni di questa soluzione alternativa aggiungono un collegamento a un piccolo file csv sullo stesso computer. Abbiamo ottenuto lo stesso risultato utilizzando una connessione dati da una tabella nel foglio di lavoro.
Crea una piccola tabella con una singola cella. La cella può avere qualsiasi cosa ma creiamo una cella con NOW (), per ragioni che spiegheremo più avanti.
Seleziona la tabella, quindi scegli Dati | Ottieni dati | Da altre fonti | da tabella / intervallo. La voce di menu esatta potrebbe essere diversa a seconda della versione di Excel.
Quando si apre lEditor di query, basta chiuderlo e caricarlo . Nel riquadro delle connessioni dati, vedrai una query.
Fai clic con il pulsante destro del mouse sulla query, scegli le proprietà per vedere il impostazioni di cui abbiamo bisogno.
Imposta la frequenza di aggiornamento che fa per te.
Per essere ordinati, noi spostare la tabella di origine (a destra) sullo stesso foglio nella query caricata (a sinistra). Poiché abbiamo utilizzato NOW () nella cella della tabella di origine, sarà facile vedere quando / se il foglio di lavoro è stato aggiornato.
Esempio di codice VBA
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
Crea un elenco di fogli di lavoro Excel automatico o un sommario
Completa la soluzione Excel NetworkDays () con le vacanze & vacanze