E ancora su Convalida. - alias: estrarre dati da fogli di cartelle chiuse - dal 04/09/04 pagina vista: volte

Ovvero: come reperire un'origine dati per Convalida su un foglio di una cartella Excel chiusa (File .xls).

Presento una possibile soluzione per tutti coloro che devono o desiderano usare Convalida sui propri fogli di lavoro. Sappiamo che usando Convalida impostata ad "Elenco", dobbiamo fornire una cella o un'intervallo di "origine" dei dati che formeranno l'elenco; sappiamo anche che se l'origine dati risiede su un foglio diverso da quello dove si usa Convalida, è opportuno definire (dal menù Inserisci/Nome/Definisci..) un nome che identifichi in maniera univoca l'intervallo di provenienza, ed usare questo "nome", preceduto dal segno uguale (=), nella finestrina dell'origine in Convalida. (Vedi articolo "Convalida in VBA" sull'altro sito). La cosa funziona anche se il foglio di origine dati è su una cartella diversa da quella dove si usa Convalida, PURCHE' entrambe le cartelle siano aperte. La sintassi da usare in questi casi dovrà prevedere il richiamo alla Cartella e al  Foglio di origine dati, da scrivere, attenzione!, non in Convalida, ma nell'origine dati che assegniamo quando di definisce il nome, ad esempio =[NomeCartellaDatiesterni.xls]Foglio1!$A$1:$A$20 . Il nome così assegnato, sarà riconosciuto da Convalida come origine dati valida, purchè entrambe le cartelle siano aperte.

Ma se vogliamo usare un'origine dati risiedente su una cartella chiusa, Convalida si rifiuta energicamente di accettare una provenienza da cartelle chiuse. Il problema lo si può risolvere ricorrendo ad un escamotage : importiamo i dati che ci servono dalla cartella chiusa, ponendoli in una cella o intervallo di celle del foglio attivo, ed usiamo questi riferimenti da assegnare all'origine in Convalida.

La procedura ovviamente è utilizzabile a piacere, anche per elenchi che formino il RowSource (oListFillRange) di ComboBox e/o ListBox, o comunque quando serva lavorare su una cartella con dati residenti su una cartella chiusa.

La procedura di base è semplice, è necessario usare una sintassi specifica per indicare ad Excel come "raggiungere" la cartella chiusa, indicando il "percorso" completo che mira al file, inserendo, come in questo esempio nella Cella di destinazione, la seguente formula :

Sub Collega()
irow = 1
'indichiamo il numero di riga
Cells(irow, 10).Formula = "='C:\TuaDirectory\[TuaCartellaChiusa.xls]Foglio1'!E1"
End Sub

Come si nota, sono presenti due apici singoli ( ' ) prima dell'unità che identifica l'Hard-Disk e immediatamente prima del punto esclamativo che delimita il nome del foglio.

Trattandosi quindi di una formula, potremo poi usare il "trascinamento" per vedere aggiornato il riferimento alla cella di origine, ed ottenere così un elenco destinazione per quanto sarà lungo l'elenco di origine sul foglio della cartella chiusa. Oppure usare AutoFill per copiare verso il basso, la formula così ottenuta, stando sicuri che otterremo l'aggiornamento automatico dei riferimenti, fino al termine di un'intervallo prestabilito.

L'intervallo così ottenuto sul foglio attivo, (nell'esempio è posto nella colonna 10) sarà quindi assegnato come "origine" a Convalida (o utilizzarlo come meglio crediamo).

Possiamo però automatizzare tutta la procedura. Sicuramente sapremo in quale colonna e da quale riga inizia l'origine dei dati sul foglio della cartella chiusa, mentre potremo non sapere quanto è lungo l'elenco, ipotizzando che allo stesso vengano periodicamente aggiunti dei valori. Purtroppo non risulta possibile "scandagliare" fogli di cartelle chiuse, alla ricerca di dove finisce un elenco, ma aggiriamo l'ostacolo con questa routine: il ragionamento è semplice: visto che non posso andare a "scandagliare" su un foglio chiuso, importo i dati, predisponendo un ciclo che corrisponda ad un certo numero di righe:

  • attraverso un ciclo che scorra 100 volte (oppure il numero di volte che riterrete opportuno)

  • in una cella fuori vista (ho usato la 15ma colonna, prima riga Cells(1, 15).Formula)), importo i dati DALLA cartella chiusa (sfruttando la formula tipo quella vista sopra), fornendo percorso completo di nome foglio e colonna e riga dove inizia l'elenco sul foglio chiuso.

  • il numero di riga lo rendiamo "variabile" usando la concatenazione tra la lettera di Colonna, e il valore rappresentato dal contatore "N" del ciclo, che iniziando da 1, leggerà tutte le celle del foglio di origine fino alla 100, e quindi modificherà l'istruzione nella parte finale della formula: Foglio1'!E" & N & " corrisponderà all'inizio a Foglio1'!E1 , poi E2, poi E3, ecc. ecc.

  • usiamo una condizione If che verifichi se il contenuto della cella (Cells(1, 15)) è diverso da 0 (zero). Questa condizione si verificherà (diverso da 0) fino a quando saranno presenti valori nelle celle di origine, mentre se avremo impostato un ciclo più lungo della lunghezza reale dei dati, otterremo in restituzione dalla formula inserita, appunto uno zero.

  • all'interno del ciclo For Next, usiamo un ciclo While Wend, che cercando, a partire dalla prima riga, colonna A (o dove vorrete), la prima cella libera, inserisca in questa cella la formula, se il contenuto di Cells(1, 15) sarà diverso da zero.

  • Puliamo la cella Cells(1, 15) ad ogni ciclo.

Sub CollegaElenco()
irow = 1
For N = 1 To 100
While Cells(irow, 1) <> ""
irow = irow + 1
Wend

Cells(1, 15).Formula = "='C:\TuaDirectory\[TuaCartellaChiusa.xls]Foglio1'!E" & N & ""
If Cells(1, 15) <> 0 Then
Cells(irow, 1).Formula = "='C:\TuaDirectory\[TuaCartellaChiusa.xls]Foglio1'!E" & N & ""
End If
Cells(1, 15) = ""
Next
End Sub

va da se che quando l'elenco in origine non conterrà più dati, otterremo solo zeri, e l'istruzione If salterà la registrazione nell'intervallo di destinazione, che ho posto nell'esempio nella colonna A iniziando dalla riga 1 (irow = 1).

Ecco così ottenuto un elenco da affidare a Convalida, posto sul foglio attivo, ma proveniente da un foglio di una cartella chiusa.

Se poi qualcuno volesse eliminare la domanda che Excel pone all'apertura di una cartella contenente riferimenti a cartelle chiuse, se si vuole o meno aggiornare i collegamenti, basterà porre nell'evento Workbook_Open questa istruzione :

  • Application.AskToUpdateLinks = False

Lasciando ad un pulsante il compito di lanciare la macro (prima però cancellate l'intervallo esistente sul foglio attivo, perchè il ciclo While accoderebbe i nuovi dati a quelli esistenti).

 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org