[Excel]Formula: riferimento dinamico a fogli esterni

[maxi]

Avendo la seguente formula: =SOMMA('C:\[CopiaProva.xls]Dati'!$B$6:$B$31)

* Step-1
Avrei la necessità, sempre sia possibile, di variare in modo “dinamico” solo la parte “C:\[CopiaProva.xls]Dati” (o anche solo “CopiaProva.xls”), ovvero il nome della cartella di lavoro a cui la formula fa riferimento.

Avendo circa 50 cartelle di lavoro simili a “CopiaProva.xls”, contenenti la base dati a cui fa riferimento l’unica cartella di lavoro contenente le formule per i conteggi, credo sia l’unica soluzione.
Ps. nella realtà le cartelle di lavoro sono 150 ed i fogli di calcolo sono 3 (ogni foglio calcolo dovrebbe elaborare 50 fogli dati, uno alla volta ovviamente), quindi o automatizzo un po’ le cose o sono guai.

E’ fattibile, ed in che modo?

* Step-2
Nel caso fosse fattibile, giustamente servirà fare in modo che la parte “dinamica” della formula di cui sopra (“CopiaProva.xls”) venga letta da qualche parte.
Le soluzioni che mi vengono in mente sono due (se ne avete delle migliori sono aperto a tutte le ipotesi):
a) molto grezza, quindi se possibile la eviterei: stabilire una cella predefinita nella quale digitare manualmente ogni volta la parte della formula (in formato testo) che deve variare “dinamicamente”.
Nell’esempio di cui sopra la cella in questione conterrebbe quindi la stringa: “C:\[CopiaProva.xls]Dati” (virgolette escluse)

b) questa sarebbe molto più comoda, ma non sapendo usare VBA avrei bisogno di un supporto:
ovvero l’idea sarebbe quella di creare una “Casella Combinata” (un semplice elenco a discesa tanto x capirci, utilizzando la Casella degli Strumento di excel) nella quale far comparire già i nomi (che sono “statici”) di tutte le cartelle di lavoro da usare come base dati.
Selezionando poi una voce presente nella Casella Combinata, le formule dell’intero foglio di lavoro dovrebbero variare “dinamicamente” in base alla stringa contenuta appunto nella casella combinata.

Per lo Step-2 come si deve procedere?

[Anthony47]

Ciao Maxi
Nell’ ipotesi che sul tuo foglio contenga link a 1 unico foglio esterno, prova la seguente macro:

Codice: Seleziona tutto

Sub Maxi1()
' 1a - Seleziona file
Scegli:
FullNome = Application.GetOpenFilename(Filefilter:="Excel Files (*.xls), *.xls,Tutti (*.*),*.*", Title:="Seleziona file")

' 1b - Segnala file scelto e Azione
Mess = ">>Selezionato " & FullNome & vbCrLf & ">>Foglio di destinazione:  " & AWN & vbCrLf & ">>  SI per Confermare; NO per Cambiare; CANCEL per abortire"
scelta = MsgBox(Prompt:=Mess, Buttons:=vbYesNoCancel)
If scelta = 2 Then GoTo Esci    'Cancel
If scelta = 7 Then GoTo Scegli   'NO = Riseleziona

'2 - quale e' il link attuale
MatrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(MatrLinks) Then GoTo Esci
MsgBox (MatrLinks(1))

' 2b - Segnala Link corrente e nuovo  e scelta Azione
Mess = "Vuoi sostituire >> " & MatrLinks(1) & vbCrLf & " Con >> " & Range("D1").Value & vbCrLf & ">>  SI per Confermare; NO per Cambiare; CANCEL per abortire"
scelta = MsgBox(Prompt:=Mess, Buttons:=vbYesNoCancel)
If scelta = 2 Then GoTo Esci    'Cancel
If scelta = 7 Then GoTo Scegli   'NO = Riseleziona

'3 - Cambia il link
ActiveWorkbook.ChangeLink Name:=MatrLinks(1), NewName:= _
        FullNome, Type:= _
        xlExcelLinks

Esci:
End Sub

Essa fa tre cose:
1) consente di scegliere il nuovo file su cui lavorare, utilizzando l’ interfaccia windows
2) legge quale e’ l’ attuale file su cui sono “collegate” le formule e chiede conferma
3) sostituisce il collegamento vecchio con quello nuovo.

Mi raccomando, 2 copie di backup prima di provare.

Fatti sentire, ciao.

[maxi]

Ottimo, nella pratica la macro fa il suo dovere, xò credo serva affinare/eliminare qualche passaggio.

La cartella di lavoro che contiene tutte le formule con i riferimenti a celle esterne di altre cartelle si chiama “Prova_Link_esterni.xls”.
Le altre due cartelle di lavoro, quelle che contengo solo la base dati, e sono:
QUERY_test_data.xls
Copia di QUERY.xls

La situazione iniziale vede una cella della cartella di lavoro “Prova_Link_esterni.xls” nella quale è presente la seguente formula:
='C:\Documents and Settings\Max\Documenti\[QUERY_test_data.xls]QUERY_test_data'!$B$1

Dopo aver eseguito la Macro “Maxi1”, ed aver selezionato la cartella di lavoro “Copia di QUERY.xls”, la formula della cella di cui sopra cambia in:
='C:\Documents and Settings\Max\Documenti\[Copia di QUERY.xls]Copia di QUERY'!$B$1

Quindi formalmente l’obiettivo è stato raggiunto, ma ci sono dei passaggi/messaggi della Macro non del tutto appropriati.

Ps. nel caso fosse utile, le cartelle di lavoro con la base-dati contengono solo n.1 foglio di lavoro ciascuna, il cui nome è identico al nome della propria cartella di lavoro (ovviamente escluso “.xls” finale). Noto con piacere che la Macro riesce a gestire anche il cambio contemporaneo del nome del ‘foglio di lavoro’ oltre che a quello della ‘cartella di lavoro’.
Era già tutto previsto?

Nell’immagine di seguito riporto le finestre che vengono visualizzate durante i vari passaggi di esecuzione della Macro.



In relazione all’immagine di cui sopra credo, sempre che la cosa sia fattibile, che la soluzione più rapida e chiara sia far apparire solo la terza finestra, che dovrà però visualizzare anche il riferimento al nome della cartella di lavoro appena selezionata (nell’esempio specifico “Copia di QUERY.xls”), tuttora mancante.

Riusciresti a modificare la Macro in questo senso?
Grazie
[/url]

[maxi]

mancava l'immagine ingrandita

http://img166.imageshack.us/img166/870/maxi1bo5.jpg

[Anthony47]

In effetti ti ho postato una versione intermedia della macro, quella finale e’:

Codice: Seleziona tutto

Sub Maxi1()
' 1a - Seleziona file
Scegli:
FullNome = Application.GetOpenFilename(Filefilter:="Excel Files (*.xls), *.xls,Tutti (*.*),*.*", Title:="Seleziona file")

' 1b - Segnala file scelto e Azione
Mess = ">>Selezionato " & FullNome & vbCrLf & ">>  SI per Confermare; NO per Cambiare; CANCEL per abortire"
scelta = MsgBox(Prompt:=Mess, Buttons:=vbYesNoCancel)
If scelta = 2 Then GoTo Esci    'Cancel
If scelta = 7 Then GoTo Scegli   'NO = Riseleziona

'2 - quale e' il link attuale
MatrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(MatrLinks) Then GoTo Esci
'MsgBox (MatrLinks(1))

' 2b - Segnala Link corrente e nuovo  e scelta Azione
Mess = "Vuoi sostituire >> " & MatrLinks(1) & vbCrLf & " Con >> " & FullNome & vbCrLf & ">>  SI per Confermare; NO per Cambiare; CANCEL per abortire"
scelta = MsgBox(Prompt:=Mess, Buttons:=vbYesNoCancel)
If scelta = 2 Then GoTo Esci    'Cancel
If scelta = 7 Then GoTo Scegli   'NO = Riseleziona

'3 - Cambia il link
ActiveWorkbook.ChangeLink Name:=MatrLinks(1), NewName:= _
        FullNome, Type:= _
        xlExcelLinks

Esci:
End Sub

Ti ho gia’ eliminato quello che era il secondo messaggio, serviva per il debug.
Se vuoi eliminare anche il primo, commenta la prima istruzione Scelta = e le due If successive.

Era già tutto previsto?

Nel caso che il nuovo file abbia solo un foglio, o abbia piu’ fogli di cui uno ha lo stesso nome di quello inserito nel link attuale, allora l’ operazione di relink si completa subito; se invece ci sono piu’ fogli e manca quello del link attuale, l’ operazione si completa solo dopo aver scelto, nella finestra che si apre “Selezione foglio”, quale foglio del nuovo file si vuole utilizzare.

Ciao,

[maxi]

Anthony47 .. sei grande!
Tutto ok, non ho remmato nulla, la tengo così perché funziona egregiamente ed è molto chiaro.

Vorrei però avere una conferma da te: così come è scritta, la macro sostituisce contemporaneamente il riferimento al link esterno in tutte le formule di TUTTI i fogli di lavoro presenti nella cartella di lavoro nella quale si esegue la Macro … è corretto?
Ho fatto una prova con due differenti fogli e pare funzionare in questo senso, quindi se anche avessi ad es. 10 fogli di lavoro differenti nella stessa cartella di lavoro la macro li “aggiornerebbe” tutti contemporaneamente (che tra l’altro è proprio quello che mi serviva).

Nel caso che il nuovo file abbia solo un foglio, o abbia piu’ fogli di cui uno ha lo stesso nome di quello inserito nel link attuale, allora l’ operazione di relink si completa subito; se invece ci sono piu’ fogli e manca quello del link attuale, l’ operazione si completa solo dopo aver scelto, nella finestra che si apre “Selezione foglio”, quale foglio del nuovo file si vuole utilizzare.

Fantastico! Ma tanto x capire, questa “Selezione foglio” è gestita dalla Sezione ‘3 della tua Macro oppure è una caratteristica gestita direttamente ed “automaticamente” da Excel?

Non odiarmi, quando hai tempo avrei bisogno di un ultimo aggiornamento della Macro.
Dovrei inserire un “filtro” nella Macro che la faccia funzionare solo se la DATA (es. 12/10/06) contenuta nella cella A2 (la posizione è sempre la stessa) di uno qualsiasi dei fogli delle cartelle di lavoro che uso come base-dati (ad es. “QUERY_test_data.xls”) è inferiore rispetto ad alla DATA (es. 31/12/06) presente nella cella A1 del foglio di lavoro ‘Config’ appartenente alla cartella di lavoro che contiene tutte le formule e all’interno della quale viene eseguita la Macro (nel caso specifico sarebbe il foglio ‘Config’ della cartella di lavoro “Prova_Link_esterni.xls”.
Siccome la Macro dovrò associarla ad un pulsante che creerò nel foglio ‘Config’della cartelle di lavoro “Prova_Link_esterni.xls”, se fosse possibile sarebbe utile che al click di tale pulsante, nel caso la data contenuta nella cella A2 sopra menzionata fosse maggiore (es. 01/01/07 > 31/12/06) di quella contenuta nella cella A1 sopra menzionata, la macro NON venga eseguita, nel senso che la parte delle formule relativa al riferimento esterno NON dovrà aggiornarsi (non servono avvisi di nessun tipo, basta che la macro non funzioni).
E’ fattibile?

Infine, dovrei proteggere il codice della Macro in questione sia dalla lettura che dalla scrittura.
Ho provato a proteggere ad uno ad uno tutti i fogli di lavoro della cartella di lavoro “Prova_Link_esterni.xls” (che è quella che contiene la macro), ma vedo che è possibile ugualmente accedere al Visual Basic Editor sia per vedere che per modificare la macro.
Come posso impedire l’accesso alla macro sia in lettura che in scrittura?
Ps. Dovrei impedire, sempre sia possibile, anche l’aggiunta di nuove macro che lavorino sulla cartella di lavoro in questione (Prova_Link_esterni.xls).

Grazie milleeeeee

[maxi]

- UPGRADE -

Dovrei inserire un “filtro” nella Macro che la faccia funzionare solo se la DATA (es. 12/10/06) contenuta nella cella A2 (la posizione è sempre la stessa) di uno qualsiasi dei fogli delle cartelle di lavoro che uso come base-dati (ad es. “QUERY_test_data.xls”) è inferiore rispetto ad alla DATA (es. 31/12/06) presente nella cella A1 del foglio di lavoro ‘Config’ appartenente alla cartella di lavoro che contiene tutte le formule e all’interno della quale viene eseguita la Macro (nel caso specifico sarebbe il foglio ‘Config’ della cartella di lavoro “Prova_Link_esterni.xls”.
Siccome la Macro dovrò associarla ad un pulsante che creerò nel foglio ‘Config’della cartelle di lavoro “Prova_Link_esterni.xls”, se fosse possibile sarebbe utile che al click di tale pulsante, nel caso la data contenuta nella cella A2 sopra menzionata fosse maggiore (es. 01/01/07 > 31/12/06) di quella contenuta nella cella A1 sopra menzionata, la macro NON venga eseguita, nel senso che la parte delle formule relativa al riferimento esterno NON dovrà aggiornarsi (non servono avvisi di nessun tipo, basta che la macro non funzioni).
E’ fattibile?

Dimenticavo una cosa.
In merito alla parte del post precedente qui sopra quotata, nel discorso "filtro" sarebbe utile poter aggiungere altri due diversi controlli, unitamente a quello già richiesto sopra.
In pratica vorrei che la Macro non venisse eseguita anche se è vera una sola delle seguenti condizioni:

- la data di sistema (quella del pc) è maggiore della data inserita nella cella A1 (la stessa citata nell'esempio più sopra; quindi presente nello stesso foglio dove è presente la macro);

- la cella A2 (sempre con riferimento all'esempio descritto prima) non è una cella di tipo "Data" (Nota: se faccio Formato -> Celle della cella A2, mi risulta un formato con Categoria = "Data", ma un Tipo = "*14/03/01". Vedo che è presente un asterisco (*) nel Tipo Data e non so se può creare problemi. Comunque se seleziono Tipo = "14/03/01" la visualizzazione della data presente nella cella A2 non cambia).

Ovviamente come già detto la posizione di A2 ed A1 nei rispettivi fogli e cartelle di lavoro è sempre la stessa, e conterranno sempre valori di tipo DATA.
Solo che la cella A1 non sarà modificabile, mentre la cella A2 per errore potrebbe essere modificata (ma questo non vederlo come problema da risolvere).

[Anthony47]

La macro modifica il “Collegamento a dati esterni”, quindi e’ giusto che lo cambi indipendentemente dal foglio in cui quel collegamento e’ utilizzato; se si voleva fare solo per un foglio, allora bisognava agire tramite Trova e Modifica, opzione “Cerca il formule” e “Cerca sul solo foglio di lavoro”.

Per quanto riguarda il discorso del foglio, l’ eventuale scelta del foglio e’ gestita da excel, sia che l’ operazione venga fatta manualmente (Menu ->Modifica ->Collegamenti) sia che venga fatta da macro.

Per quanto riguarda le verifiche, potresti farle in un’ altra macro, quella attivata dal pulsante, e solo se la verifica e’ Ok esegui l’ istruzione Call Maxi1 che esegue la macro che ti ho passato.

Infine la protezione: fai rightclick sul VbaProject, scegli Proprieta’, scegli il tab Protezione; qui potrai spuntare “Proteggi da visualizzazione” con password.

Per proteggere il foglio dall’ azione di altre macro attivate dall’ utente, la cosa che mi viene in mente e' di proteggere i fogli di lavoro con password, in modo che al massimo si possono leggere dati ma non modificare.
Per proteggere e sproteggere dalle tue macro, potrai usare istruzioni quali

Codice: Seleziona tutto

ActiveSheet.Protect Password:="PasswordSegreta"
ActiveSheet.Unprotect Password:="PasswordSegreta"

Ciao,

[maxi]

Per quanto riguarda le verifiche, potresti farle in un’ altra macro, quella attivata dal pulsante, e solo se la verifica e’ Ok esegui l’ istruzione Call Maxi1 che esegue la macro che ti ho passato.

Purtroppo sono a zero con VBA, quindi anche se c’è da aspettare un po’ attenderei comunque il tuo supporto, naturalmente compatibilmente ai tuoi tempi.

Da parte mia, eliminando anche qualche controllo ridondante, cerco di raggruppare in modo più semplice possibile il resoconto delle verifiche che la nuova Macro dovrebbe svolgere prima di “richiamare” ‘Maxi1’, in modo da evitarti altre perdite di tempo.

Quindi la “vecchia” macro ‘Maxi1’ dovrebbe essere richiamata SOLO SE:

1) la DATA (es. 12/10/06) contenuta nella cella A2 (la posizione è sempre la stessa per tutte le cartelle di lavoro) di una qualsiasi cartella di lavoro che uso come base-dati (in pratica quelle selezionate durante l’esecuzione della macro ‘Maxi1’ e che aggiornano il “riferimento esterno” in tutte le formule; nel caso specifico ad es. “QUERY_test_data.xls”) è inferiore rispetto ad alla DATA presente nella cella A1 (es. 31/12/06; anche qui la posizione è sempre la stessa) del foglio di lavoro ‘Config’ (quello in cui realizzerò il pulsante associato alle macro; quindi credo si possa usare semplicemente ‘ActiveSheet‘) appartenente alla cartella di lavoro che contiene tutte le formule ed all’interno della quale viene appunto eseguita la Macro (nel caso specifico “Prova_Link_esterni.xls”).

2) la DATA di sistema (quella attuale del pc) è inferiore rispetto alla Data inserita nella cella A1 (che è la stessa cella già citata qui sopra nel punto 1; quindi quella del foglio di lavoro ‘Config’ appartenente alla cartella di lavoro che contiene tutte le formule … e quindi tutte le macro).

Resto a disposizione nel caso servissero informazioni più dettagliate
Grazie