Lavorare con dati contenuti in cartelle .xls chiuse. - pagina vista: volte

Sappiamo che, a parte metodi avanzati, per lavorare, interrogare, usare dati contenuti in altre cartelle diverse dalla cartella attiva, siamo costretti ad aprire entrambe le cartelle (o più di due cartelle) perchè molte routine o istruzioni non lavorano con dati di cartelle non aperte.

Per "metodi avanzati"  mi riferisco ad istruzioni e procedure vba come quelle presentate da Elio Buonocore (vedi sezione "Lavori", articoli "Elio Buonocore (2)" e "(3)") che per essere affrontate (come progettazione) richiedono una buona conoscenza del vba.

Esiste tuttavia la possibilità di ottenere dati da cartelle xls chiuse, usando ad esempio funzioni del foglio di lavoro, funzioni in cui nell'argomento "provenienza dati" si può usare al posto del solito intervallo normalmente dichiarato, una sintassi che miri ad un intervallo di un foglio di una cartella chiusa. Ne è un esempio l'articolo "Doppi Dati Controllo", sul sito ennius.interfree dove è esemplificato come usare il CERCA.VERT (VLOOKUP) su una cartella chiusa.

Esiste quindi, per una cella di un foglio di lavoro, la possibilità di importare (collegare è detto meglio) il dato contenuto in una cella di una cartella chiusa, inserendo sul foglio attivo, come una formula (con il segno =), una stringa di testo che altro non è che una istruzione di uguaglianza; è necessario rispettare una sintassi, come nell'esempio che vediamo (compreso i singoli apici e le parentesi quadre):

  • ='C:\Temp\[AltraCartella.xls]Foglio1'!A1

dove indichiamo il "percorso" che mira alla cartella (xls) chiusa, al foglio su cui è la cella, il riferimento alla cella, quindi:

  • Lettera che identifica l'Unità (l'hard-disk) (C:\)

  • Nome della Cartella (ed ev. sottocartelle) sull'Unità dove risiede la cartella xls (Temp)

  • Nome della cartella.xls (è il file xls) (AltraCartella.xls)

  • Nome del foglio   (Foglio1)

  • Riferimento alla cella contenente il dato   (A1)

In definitiva, con la formula, noi diciamo: rendimi il contenuto di questa cella X (foglio attivo), uguale al contenuto della cella che si trova nella cartella.xls tal dei tali (e si indica il percorso).

Ovviamente la formula è compilabile come istruzione vba, e se noi volessimo la cella A1 del Foglio1 della cartella xls attiva uguale ad una cella di una cartella chiusa, potremmo usare una macro così:

  • Sub MacroTua()
    Sheets(1).Range("A1").Formula = "='C:\Temp\[
    AltraCartella.xls]Foglio1'!A1"
    End Sub

Il dato che noi otterremo nella cella A1 del foglio attivo lo potremo così usare per successivi passaggi o calcoli, o quel che ci pare. Sarà solo un normale dato restituito da una formula.

Quindi questa soluzione ci consente di ottenere dati da una cartella xls chiusa, conoscendo però il riferimento preciso di quale cella vorremo il dato.

Uno dei problemi che spesso riscontriamo, è che non ci ricordiamo a quale cella, (sulla cartella chiusa) collegarci, oppure per effetto di aggiunte o inserimenti (sulla cartella chiusa), la cella che vogliamo collegare si è spostata, o magari vorremmo usare una routine vba che usi un ciclo For..Next, per la ricerca di un dato in una tabella (sulla cartella chiusa), oppure un'istruzione Select, e le nostre routine non riescono a lavorare in quanto impossibilitate a lavorare su cartelle chiuse.

La soluzione potrebbe essere quella di importare (con l'istruzione vista sopra) tutta un'area contenente dati, oppure addirittura tutto un foglio (quasi), usando un foglio ad hoc lasciato o aggiunto sulla cartella attiva, in modo da avere una "copia" dei dati contenuti sulla cartella chiusa. Potremo così avere i dati a disposizione sulla cartella attiva, lavorarci, eseguire ricerche su i dati "copia", e magari alla fine del lavoro, pulire (ClearContents) tutte le celle sul foglio ad hoc, o l'intero foglio.

Operazione questa che consiglio come necessaria (tanto basterà rilanciare la macro, al bisogno) per evitare due cose:

  • Quando riapriremo la cartella attiva (una volta chiusa e salvata) Excel sentirà che esistono collegamenti a celle di cartella chiusa e ci potrà chiedere se vorremo aggiornare i collegamenti. Se avremo pulito le celle, no.

  • I dati "copia" sul foglio ad hoc, appesantiscono il file inutilmente.

Come procediamo? Usiamo l'istruzione vba vista sopra, per inizializzare il collegamento ad una cella di cartella chiusa, e poi usiamo il metodo AutoFill (equivale a "trascinamento" (automatico)) per copiare la formula inserita, in un'intervallo prestabilito, lasciando che Excel provveda ad aggiornare i riferimenti alle celle.

Mi sembra semplice come concetto, no?

Dovremo usare un doppio trascinamento, prima in verticale, impostando la cella iniziale e l'ultima cella della colonna iniziale come intervallo di  "Destination", e poi usare questo intervallo per il trascinamento in orizzontale. Potremo quindi collegarci ad una determinata area, o a tutto il foglio (quasi). Vediamo due esempi:

esempio collegando solo un'area, da A1 a H30 : prima si inserisce la formula in A1, poi si "riempono" (Fill) le celle dell'intervallo fino a A30, quindi si setta una variabile (X) come intervallo A1.A30, indi si "riempe" lo stesso intervallo di 30 celle ma andando a destra fino alla colonna H.

  • Sub CollegaArea()
    Range("A1").Formula = "='C:\Temp\[AltraCartella.xls]Foglio1'!A1"
    Range("A1").AutoFill Destination:=Range("A1:A30"), Type:=xlFillDefault
    Set X = Range("A1:A30")
    X.AutoFill Destination:=Range("A1:H30"), Type:=xlFillDefault
    End Sub

Chiaramente saremo noi ad assegnare l'intervallo, variando opportunamente i riferimenti, e conoscendo ovviamente la dimensione e la locazione dell'intervallo di origine. Non sapendo invece dove e quanto sarà grande l'intervallo di origine, potremmo collegare tutto un foglio, iniziando per forza di cose dalla cella A1;  esiste tuttavia un limite alla quantità di celle collegabili, che NON consente di importare tutto un foglio: questo limite è rappresentato dal numero di righe massime collegabili, che possiamo indicare in 32500 righe, mentre potremo importare tutte le colonne (fino alla IV).

MA: attenzione alle risorse di sistema: potreste bloccare il computer; bisogna considerare che la potenza del proprio computer, (e la routine può determinare tempi lunghi di esecuzione), è sfruttata anche da altri programmi, compresi tutti quelli che girano in background, e che assorbono risorse anche loro.

Direi quindi che non sarebbe male limitarci a collegare aree anche ampie, ma definite, come ad esempio, fino alla riga 30000 e alla colonna 100, come nell'esempio sotto, ed usiamo il Foglio3 per ottenere il collegamento:

  • Sub CollegaFoglio()      'quasi
    Sheets("Foglio3").Range("A1").Formula = "='C:\Temp\[AltraCartella.xls]Foglio1'!A1"
    Sheets("Foglio3").Range("A1").AutoFill Destination:=Range("A1:A30000"), Type:=xlFillValues
    Set SourceRange = Sheets("Foglio3").Range("A1:A30000")
    Set fillRange = Sheets("Foglio3").Range("A1:CV30000")
    SourceRange.AutoFill Destination:=fillRange, Type:=xlFillValues
    End Sub

Per pulire le celle prima di chiudere la cartella, potremo usare una istruzione del genere:

  • Sub Pulisci()
    Sheets("Foglio3").Cells.ClearContents   
    'puliamo tutto il foglio, oppure meglio, definire l'intervallo da pulire visto 'che lo conosciamo,così: Sheets("Foglio3").Range("A1:CV30000").ClearContents
    End Sub

Queste soluzioni non sono delle migliori, soprattutto con fogli origine pieni zeppi di dati, ma possono validamente aiutare tutti coloro che fanno un uso "normale" di Excel (in termini di quantità di dati presenti su fogli chiusi), e comunque era un'idea da segnalare. Per quanto riguarda il tipo di Formato dei dati "collegati", per non appesantire troppo le routine viste sopra, conviene intervenire manualmente sulle celle o intervalli contenenti formati specifici, come ad esempio date.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org