Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

macro esterna per files excel aperti

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

macro esterna per files excel aperti

Postdi BG66 » 22/11/17 15:46

Ciao,
al momento il mio è un interrogativo più che la richiesta di aiuto fattivo.......che seguirà se la risposta è positiva :lol:

In pratica, ho 12 files excel aperti 24 ore su 24 per 5 gg su 7. In questi files (uno per impianto) i miei collaboratori inseriscono i dati dei pezzi prodotti. Ognuno di questi file ha una macro che in orari prestabiliti (3 volte al giorno) avvia una macro di aggiornamento dei dati produttivi.
Per ragioni non ancora comprese (linea dati occupata, server o altra diavoleria) non sempre i dati "passano".
Si può fare un "qualcosa" che lancio a mano e che forzatamente: avvia la macro,chiude e salva tutti i file presenti in una cartella specifica?

Sperando di essere stato sufficientemente chiaro.
Grazie per la risposta.
Ultima modifica di BG66 su 22/11/17 15:51, modificato 1 volte in totale.
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Sponsor
 

Re: macro esterna per files excel aperti

Postdi Anthony47 » 22/11/17 20:31

Do' per scontato che ogni file sia aperto e modificato solo su un Pc (ma e' meglio se lo confermi)

Do' anche per scontato che se una macro dice Workbooks("NomeWb").Save allora il file venga salvato; quindi dovresti spiegare il significato di "non sempre i dati passano".

Quanto alla chiusura forzata, questa va fatta sul Pc che tiene aperto il file; cioe' sullo stesso Pc che dici non sempre fa il suo dovere (ragione in piu' per capire quello che oggi "non passa").

Comunque io direi che "probabilmente si puo' fare", anche se non ho capito bene che cosa bisognerebbe fare, perche' non ho capito bene quale e' il problema da aggirare :D

Con questa previsione direi che la prima cosa da chiarire e' che cosa oggi non funziona e come ogni file viene condiviso. Poi si potra' affinare il discoroso.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 22/11/17 21:48

Ciao Anthony,
in seguenza:
a) i 12 files sono suddivisi su 3 pc (3 cad x pc), sono aperti in contemporanea e il più delle volte tutti ridotti ad icona contemporaneamente (infatti per la macro AggPROD avevo usato active.workbook ma la riduzione ad icona sballava il tutto).

b) Non passa significa che alcune volte (e al momento non capisco perchè) e solo in alcuni files ( e non sempre gli stessi!!) trovo i dati NON aggiornati rispetto all'orario imposto dalla macro on time.

In questa cartella di lavoro:
Codice: Seleziona tutto
Private Sub Workbook_Open()
Application.OnTime TimeValue("06:06"), "AggPROD"
Application.OnTime TimeValue("14:06"), "AggPROD"
Application.OnTime TimeValue("22:06"), "AggPROD"

End Sub


In modulo standard:
Codice: Seleziona tutto
Sub AggPROD()
    Dim LastB As Long
    Application.ScreenUpdating = False
    With ThisWorkbook
      .Unprotect Password:="pippo"
      With .Sheets("PRODUZIONE")    '<<<
        .Unprotect Password:="pippo"
        LastB = .Cells(.Rows.Count, "B").End(xlUp).Row
        'Copia formule:
        .Range("M2:P2").Copy .Range(.Range("M3"), .Range("M" & LastB))
        'Copia valori:
        .Range(.Range("M3"), .Range("P" & LastB)).Copy
        .Range("M3").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Protect Password:="pippo"
      End With
      .Protect Password:="pippo"
    End With
    Application.ScreenUpdating = True
    ThisWorkbook.Save
   
End Sub


Cosa vorrei fare:
Quando mi accorgo che i dati non sono aggiornati, evitare di andare su 3 pc diversi e lanciare la macro manualmente.
Ma lanciare uno script/ batch anche non da excel che "forzi" manualmente l'avvio della macro AggPROD.

Grazie ancora
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi Anthony47 » 23/11/17 00:16

No, aspetta... Limitiamoci a un singolo file:
Su quanti Pc e' aperto contemporaneamente? Tutti i Pc devono inserire e salvare modifiche (sullo stesso file)? Con quale tecnica e' condiviso tra te e gli altri utenti? Si tratta di file dichiarati condivisi tramite Menu /Revisione /Condividi cartella di lavoro?

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 23/11/17 06:04

Scusami Anthony, ti stò portando fuori strada.
Il singolo file è aperto una sola volta su un singolo pc.

Forse con un'esempio riesco a farmi capire:
postazione 1: file aperti ma ridotti ad icona T09 - T10 - T12 -T14
postazione 2: file aperti ma ridotti ad icona T15 - T16 - T18 -T19
postazione 3: file aperti ma ridotti ad icona T11 - T03 - T05 -T08
Il percorso cartella è comune per tutti: P:\forni2017\novembre\

Non viene usata la tecnica della condivisione in primis perchè non la conoscevo ma in ogni caso NON mi serve perchè l'operatore ha impianti specifici assegnati.

Ciao
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi Anthony47 » 23/11/17 11:58

Ci stiamo avvicinando...
Benissimo il fatto che un certo file sia aperto su un unico Pc (es "Postazione1"), quello che deve modificarlo e che periodicamente esegue la AggPROD.
Ma veniamo al tuo Pc:
1) Se il file e' apero sulla Postazione1 tu lo puoi aprire in sola-lettura, e vedi la situazione del file cosi come risulta nel momento dell'apertura, o meglio: come risultava l'ultima volta che era stato salvato da Postazione1. Questo per sottolineare che eventuali modifiche fatte e salvate da Postazione1 dopo che tu il file l'hai aperto non ti saranno visibili fintanto che non chiudi e riapri la tua copia del file.

Questo ti accende qualche lucina, sul perche' a volte non vedi le modifiche fatte dalle varie PostazioniX?

2) Se vuoi essere confidente che i 12 file vengano aggiornati, potresti mettere in una cella specifica di ogni file l'orario di salvataggio.
Ad esempio aggiungendo questa riga in fondo alla tua AggPROD:
Codice: Seleziona tutto
    Application.ScreenUpdating = True
    ThisWorkbook.Sheets("UnFoglioNoto").Range("LaCellaDefinita").Value = Now     '<<< AGGIUNGERE
    ThisWorkbook.Save

Oppure (in alternativa alla modifica su AggPROD), se vuoi tenere traccia anche di eventuali salvataggi fatti dall'operatore, inserisci nel modulo QuestaCartellaDiLavoro di ognuno dei 12 file:
Codice: Seleziona tutto
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("UnFoglioNoto").Range("LaCellaDefinita").Value = Now 
End Sub

Poi in un tuo file di monitoraggio inserisci un "collegamento" a quel file, quel foglio e quella cella.
Se hai un collegamento a ognuno dei 12 file potrai vedere sul tuo file di monitoraggio l'ora dell'ultimo salvataggio facendo Aggiorna tutti, in Menu /Dati /gruppo Connessioni, e i dati vengono aggiornati a quel momento (senza bisogno di aprire i 12 file target).

Oppure ti fai una macro che contiene l'istruzione
Codice: Seleziona tutto
    ActiveWorkbook.RefreshAll
e poi la colleghi a un pulsante sul tuo foglio di monitoraggio

Per evitare equivoci, il "collegamento" e' quella cosa che si imposta quando ad esempio:
-hai il file A e il file B aperti
-in file A scrivi "uguale" nella barra della formula (il carattere =, per cominciare una formula), poi vai su file B, selezioni un foglio, selezioni una cella (o un'area; ma nel nostro esempio selezionerai la cella che contiene l'ora di salvataggio), e premi Enter per completare la formula.
-chiudi il file B, vedrai ua formula del tipo
Codice: Seleziona tutto
='Drive:\Percorso\completo\[NomeDelFileB]NomeDelFoglio'!IndirizzoCella


3) Ovviamente se ti interessano anche altri dati dai 12 file puoi scrivere tante altre formule che collegano il tuo file di monitoraggio alle varie celle, in modo da avere una overview piu' significativa. Se il metodo ti interessa, allora dopo aver scritto le formule coi collegamenti al primo file puoi facilmente creare le formule per il secondo, il terzo etc file:
-copi le formule del primo file e le incolli nell'area successiva
-lasciando la selezione su questa seconda area, fai un Trova + Sostituisci; ti bastera' sostituire (cercando nelle Formule) il nome del primo file con quello del secondo, poi del terzo etc etc
Questo ovviamente presuppone che la struttura dati dei 12 file sia esattamente la stessa.


Se ora ti ho portato io fuori strada allora so che mi perdonerai...

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 25/11/17 17:15

[RISOLTO]
Ciao Anthony,
sicuramente non fuori strada ma per quello che mi riguarda solamente alcune ore ben impiegate di una giornata uggiosa. ;)

Grazie mille.
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi Anthony47 » 27/11/17 19:48

Si, pero' sarei curioso di sapere come hai impiegato il sabato pomeriggio uggioso...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 01/12/17 13:54

Ciao Anthony,
...diciamo che è stato impiegato meglio :lol:
Mentre per questo thread sono di nuovo al via (...passando per la galera!!), in pratica DEVE esserci una condizione che in maniera random non permette ad AggPROD di avviarsi (vedi immagine dove solo alcuni impianti hanno "trasmesso" l'informazione ma al massimo 1 volta se 3 :!: ):
Immagine

A questo punto meglio essere draconiani e quindi è fattibile creare uno script excel o non excel che:
1) và in una cartella specifica sul nas aziendale
2) verifica se i file sono aperti ( probabile al 90%), avvisa che il programma è in chiusura, fa partire AggPROD e poi li chiude.

La riapertura sarà a cura di quelli che vengono!!

Grazie per l'aiuto
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi Anthony47 » 01/12/17 19:57

Non ho capito se alla fine hai seguito l'idea di creare un file di monitoraggio che estrae dai file di Impianto tramite "Collegamenti".
Se l'hai fatto allora mi accorgo di una cosa: il comando AggiornaTutti non aggiorna i collegamenti ai file remoti chiusi (lo fa se fai AggiornaValori nella procedura di Modifica Collegamenti).
Per avere il dato aggiornato dal "Collegamento" devi riconfermare la formula nella cella.

Puoi ottenere questo con una macro come questa:
Codice: Seleziona tutto
Sub RemoteUpd()
Dim toMonitor As Range, Cella As Range
'
Set toMonitor = ThisWorkbook.Sheets("Foglio1").Range("A1:C5")         '<<< L'area con i collegamenti remoti da aggiornare
For Each Cella In toMonitor
    If Cella.HasFormula Then Cella.Formula = Cella.Formula
Next Cella
End Sub
La riga marcata <<< e' da aggiornare secondo la tua situazione (non e' necessario che tutta l'area indicata contenga collegamenti; potrebbe anche contenere altre formule o costanti o niente)
Quando vuoi vedere i dati aggiornati lancerai la Sub RemoteUpd (che potrebbe anche essere oggetto di una esecuzione periodica sul tuo pc tramite OnTime)

Ciao


Per quello che ne so non esiste un modo semplice per cui dal mio Pc comando la chiusura di un file su un altro Pc. Lo potrei fare ad esempio compilando un dato condiviso che contiene la richiesta di chiudere il file; ma poi e' il secondo Pc che deve leggere questa richiesta e trasformarlo in comando di chiusura del file o di Excel. Ma questo meccanismo e' piu' complesso di quello che al momento sembra che non giri.
Ma hai utilizzato una cella per memorizzare la data/ora di esecuzione della AggProd (da visualizzare poi nel tuo file di monitoraggio)?

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 02/12/17 06:34

Ciao Anthony,
è proprio grazie al monitoraggio che ho avuto la certezza ....che c'è qualcosa che non capisco!!
Immagine

Come vedi un solo impianto ha correttamente eseguito l'aggiornamento imposto (TNT8), gli altri sono bellamente fermi a quando ho forzato la macro facendola partire "manualmente per ogni files dalle postazioni specifiche.
Conseguentemente il riassunto potrebbe essere:
1) la macro funziona perchè lanciate dal pc dove risiede il singolo file accetta ed esegue l'istruzione,
2) la macro on time funziona perchè altrimenti nessun files si dovrebbe aggiornare.
3) la data/ora del pc delle 3 postazioni sono corrette quindi on time non ha motivo di non attivarsi o di darmi un informazione errata
4) l'excel è correttamente abilitato ad eseguire le macro altrimenti non avrebbe eseguito la forzatura manuale (vedi punto1)

Quindi resta il mio dubbio, ossia c'è una condizione (riduzione ad icona, più file aperti nella stessa sessione excel o chissa cos'altro) che "boicotta", in maniera random, l'esecuzione di AggPROD.

:cry:
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi Anthony47 » 02/12/17 13:06

Hummm.... vediamo se siamo vittime di un paio di clamorosi equivoci...

1) Tu usi
Codice: Seleziona tutto
Private Sub Workbook_Open()
Application.OnTime TimeValue("06:06"), "AggPROD"
Application.OnTime TimeValue("14:06"), "AggPROD"
Application.OnTime TimeValue("22:06"), "AggPROD"
End Sub

Ti e' chiaro che in questo modo, all'apertura del file, tu pianifichi una esecuzione della macro alle 6:06, un'altra alle 14:06 e una terza e ultima alle 22:06?
Cioe' dopo queste 3 esecuzioni la pianificazione e' finita e non si replica piu', salvo che non chiudi e riapri il file. Vedi Nota* successiva.


2) In un'altra discussione (viewtopic.php?f=26&t=109055) la Sub AggPROD conteneva un MsgBox, e la discussione verteva su come evitare il messaggio in certe circostanze.
Ti e' chiaro che se la AggPROD lanciata "OnTime" contiene il MsgBox, la finestra del messaggio rimane aperta fintanto che l'utente non preme Ok, impedendo in questo modo al codice di completarsi ed eventualmente alla prossima macro OnTime di avviarsi (se nelle 8 ore nessuno ha premuto Ok)?
Quindi spero che la AggProd che viene eseguita OnTime non contenga msgbox, o che essi vengano inibiti se la macro e' lanciata OnTime.


Nota* in merito all'OnTime.
Le tre pianificazioni citate sono riferite all'orario corrente. Questo significa che se apri il file a mezzogiorno allora pianifichi 2 esecuzioni oggi e una per domattina.
Le pianificazioni rimangono in memoria fintanto che Excel come programma non venga chiuso; quindi se il file venisse chiuso e riaperto nel corso della giornata potresti trovarti con piu' pianificazioni alla stessa ora (cioe' la macro potrebbe essere eseguita piu' volte allo stesso orario).

Per questo e' opportuno che inserisci nei file una Sub Workbook_BeforeClose che stoppi eventuali schedulazioni impostate:
Codice: Seleziona tutto
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime TimeValue("06:06"), "AggPROD", , False
Application.OnTime TimeValue("14:06"), "AggPROD", , False
Application.OnTime TimeValue("22:06"), "AggPROD", , False
On Error GoTo 0
End Sub


Se scopri che il problema e' legato all'equivoco "#1" allora vedremo come modificare la pianificazione per renderla veramente continua.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 02/12/17 13:47

Ciao Anthony,
confermo che tutti i files, tranne accidenti, vengono aperti il lunedi ore 6.00 e chiusi solamente il sabato mattina ore 6.00.
E quindi il fatto che avevo/ho dato per scontato che OnTime ciclasse autonomamente è l'errore che mi trascino dall'inizio del progetto :undecided:

Riassumo le 3 macro, che a seguito delle modifiche post thread precedente, lavorano nel file master:

la prima:
Codice: Seleziona tutto
Private Sub Workbook_Open()
   
    Dim nome
    Dim anno
    Dim data
        With ThisWorkbook
            With Sheets("forno_T09")   '<<<
            .Unprotect Password:="pippo"
    nome = Split(ThisWorkbook.Name, "_")
    anno = Split(nome(2), ".")
    data = CDate("1 " & nome(1) & " " & anno(0))
    If Cells(2, 1) <> data Then Cells(2, 1) = data
    Application.OnTime TimeValue("06:06"), "CallAggPROD"
    Application.OnTime TimeValue("14:06"), "CallAggPROD"
    Application.OnTime TimeValue("22:06"), "CallAggPROD"
            .Protect Password:="pippo"
            End With
        End With
       
End Sub

La seconda e la terza accodate nello modulo 1:
Codice: Seleziona tutto
Sub AggPROD()
    Dim LastB As Long
    Application.ScreenUpdating = False
    With ThisWorkbook
      .Unprotect Password:="pippo"
      With .Sheets("PRODUZIONE")    '<<<
        .Unprotect Password:="pippo"
        LastB = .Cells(.Rows.Count, "B").End(xlUp).Row
        'Copia formule:
        .Range("M2:P2").Copy .Range(.Range("M3"), .Range("M" & LastB))
        'Copia valori:
        .Range(.Range("M3"), .Range("P" & LastB)).Copy
        .Range("M3").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Protect Password:="pippo"
      End With
      .Protect Password:="pippo"
    End With
    Application.ScreenUpdating = True
    ThisWorkbook.Sheets("forno_T09").Range("AP5").Value = Now     '<<< inserisce data ultimo aggiornamento
    ThisWorkbook.Save
 End Sub


Codice: Seleziona tutto
Sub CallAggPROD()
        AggPROD
        MsgBox "Aggiornamento T09 effettuato"

 End Sub


Grazie per l'aiuto.
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi Anthony47 » 02/12/17 19:16

Ecco spiegato...
Nel modo adottato non solo si ritrovano solo 3 esecuzioni schedulate, ma se l'operatore non preme Ok ci si ferma alla prima.

Facciamola semplice, almeno ci provo:
1) Crea una nuova Sub Schedula:
Codice: Seleziona tutto
Sub Schedula()
On Error Resume Next
Application.OnTime TimeValue("06:06"), "Schedula", , False
Application.OnTime TimeValue("14:06"), "Schedula", , False
Application.OnTime TimeValue("22:06"), "Schedula", , False
On Error GoTo 0
Call AggPROD
Application.OnTime TimeValue("06:06"), "Schedula"
Application.OnTime TimeValue("14:06"), "Schedula"
Application.OnTime TimeValue("22:06"), "Schedula"
End Sub

2) Nella Workbook_Open, sostituisci le attuali 3 righe di Application.OnTime TimeValue con
Codice: Seleziona tutto
On Error Resume Next
Application.OnTime TimeValue("06:06"), "Schedula", , False
Application.OnTime TimeValue("14:06"), "Schedula", , False
Application.OnTime TimeValue("22:06"), "Schedula", , False
On Error GoTo 0
Application.OnTime TimeValue("06:06"), "Schedula"
Application.OnTime TimeValue("14:06"), "Schedula"
Application.OnTime TimeValue("22:06"), "Schedula"

Il codice della Sub AggPROD e della Sub CallAggPROD rimane invariato.

La logica e' che all'apertura si eliminano eventuali schedulazioni rimaste pendenti e si impostano le nuove.
Lo stesso procedimento e' contenuto nella nuova Sub Schedula, che e' la macro eseguita su OnTime: si eliminano le schedulazioni pendenti, si esegue la AggProd, si impostano le prossime schedulazioni.

Incrociando le dita...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15538
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: macro esterna per files excel aperti

Postdi BG66 » 03/12/17 07:25

Ciao Anthony,
all'alba e in remoto ho modificato tutti i files interessati.
Domani avremo la risposta finale.

Grazie all'ennesima potenza.
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44

Re: macro esterna per files excel aperti

Postdi BG66 » 05/12/17 08:39

[RISOLTO]
Tutto perfettamente funzionante.Sei un mito!! ;)

Grazie
BG66
Excel2010
BG66
Utente Senior
 
Post: 119
Iscritto il: 20/08/16 07:44


Torna a Applicazioni Office Windows


Topic correlati a "macro esterna per files excel aperti":


Chi c’è in linea

Visitano il forum: Statix e 24 ospiti