Dati (valori) da file .xls chiusi e protetti da password. - pagina vista: volte

Sui due siti ennius esistono già articoli che spiegano come ottenere dati (valori) da file chiusi (vedere anche: Lavorare con dati contenuti in cartelle .xls chiuse), ma una recente domanda ha evidenziato un problemino presente con dati provenienti da files .xls chiusi protetti da password ; la domanda:

"...il file a cui fa riferimento la cella sono protetti da password, mi chiede sempre la password, ci sarebbe 1 modo per evitare questa domanda inserendo la/le Pw nel codice? ..."

Intanto va ricordato che in ambito Excel, i dati provenienti da altri file .xls (Workbooks) sono accettati come "collegamenti" e che Excel "LEGGE" la presenza di "collegamenti" a dati su altre cartelle .xls (files) chiuse, e si affretta a chiederci se vogliamo aggiornare detti "collegamenti". Quando una o più celle sono "Collegate" e contengono collegamenti di tipo xlExcelLinks con istruzioni simili a: ='C:\TuaDirectory\[NomeFile.xls]Foglio1'!A1, sono viste come formule residenti e "lette" da excel, che fa la richiesta. (Altra cosa da ricordare è che ogni file.xls, nella terminologia Microsoft, viene anche definito come "cartella", generando a volte confusione tra i neofiti di excel, che possono interpretare il termine come "directory"; tranquillizziamoli dicendo che quando si parla di Excel, "cartella" equivale a file, a meno che non si stia parlando di "percorsi").

Questa richiesta (peraltro più che giustificata), può essere eliminata automatizzando la risposta mediante inserimento nell'evento Workbook_Open (del file "chiamante") di opportune istruzioni, vediamo alcune istruzioni:


1) - Eliminare la finestra che richiede l'aggiornamento con aggiornamento automatico dei collegamenti:  Application.AskToUpdateLinks = False   

(la proprietà AskToUpdateLinks ha valore True se all'apertura di un file con collegamenti viene richiesto all'utente di aggiornarli oppure ha valore False se i collegamenti vengono aggiornati automaticamente. Proprietà di tipo Boolean di lettura-scrittura).
 

2) - Evitare la comparsa della richiesta e confermare con unica istruzione di volere l'aggiornamento:

  • ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways

3) -  Evitare la comparsa delle richieste e confermare con unica istruzione di NON volere l'aggiornamento:

  • ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
     

Ma passiamo al problema: se la cartella .xls da cui provengono i dati è protetta da password, prima ancora della richiesta di aggiornare i dati, compare una finestra di dialogo che chiede di inserire la password di accesso al file, e ribadisco di "accesso" al file, NON di "apertura file"; infatti la protezione tramite password si esplica anche per il solo accesso ai dati che sono sulla cartella chiusa, non solo per l'apertura della cartella stessa. Un immagine della dialogbox:

Bene, anzi, male, perchè quella finestra di dialogo NON consente, blocca, l'esecuzione di istruzioni vba, compreso l'invio tramite l'istruzione SENDKEYS sia della eventuale password che ovviamente dovremo conoscere, sia della sequenza tasto INVIO, e questo nonostante SENDKEYS dovrebbe inviare i comandi alla "finestra attiva" in quel momento, cioè la dialogbox per l'inserimento di password. (dialogbox che è a modalità obbligata, cioè fino a che non si scrive e/o non si agisce sui pulsanti non si può agire neppure su excel nè eseguire istruzioni vba)

Quindi diventerebbe impossibile accontentare la domanda del "pellegrino" che chiedeva l'automatismo per l'inserimento della password, a meno di non usare l'escamotage di "aprire" il file da cui provengono i dati, e subito dopo aver lasciato aggiornare i collegamenti, "richiudere" detto file (o files); infatti nell'istruzione di apertura di un file (Workbooks.Open) è possibile usare diversi "argomenti", compreso l'immissione di password di lettura e/o scrittura, come in questo esempio dove dal file Cartel2.xls apriamo un file Cartel1.xls protetto in lettura e scrittura dalla password "lui" (vedi guida in linea per la descrizione degli argomenti)

  • Dim wkb As Workbook
    Set wkb = Application.Workbooks.Open("C:\Cartel1.xls", 3, , , Password:="lui", WriteResPassword:="lui") 
    'apriamo il file Cartel1.xls sproteggendolo
    Workbooks("Cartel2.xls").UpdateLinks = xlUpdateLinksAlways   
     'aggiorniamo i collegamenti sulla cartella ora non attiva, Cartel2.xls
    wkb.Close  
    'chiudiamo Cartel1.xls che era diventata la cartella attiva

Soluzione questa sopra che non risolve la domanda e il problemino postoci, ma cercando una soluzione, quel gran "pozzo a perdita" che è il web, rovista rovista, un'aiuto l'ha dato: sul sito http://www.j-walk.com/ss/excel/tips/tip82.htm ho trovato istruzioni che consentono l'accesso a dati provenienti da cartelle .xls chiuse sfruttando NON già "collegamenti (OLE)" ma una funzione basata sul metodo Execute  applicato a (vecchia) Excel4Macro.

E allora ?, si dirà, cambia il modo di accedere ai dati su cartelle .xls chiuse, ma se la provenienza è protetta da password come lo superiamo l'ostacolo della dialogbox vista sopra?

Semplicemente perchè con la funzione basata su Excel4macro NON abbiamo celle direttamente collegate (tipo ='C:\TuaDirectory\[NomeFile.xls]Foglio1'!A1) ed Excel NON leggendo "collegamenti" NON avvia la dialogbox incriminata. A quel punto, lo vedremo negli esempi, sarà possibile usare SENDKEYS senza problemi.

Le istruzioni.

La funzione utente usata (PrendiDati(arg)) richiede 4 argomenti per agire: il percorso completo che mira al file (unità compreso directory ev sottodirectory) nome del file .xls, nome o indice del foglio, riferimento cella (dove risiede il dato collegato)

Per assegnare gli argomenti useremo una normale macro che inseriremo in un modulo standard, lo stesso dove avremo inserito la funzione, e richiameremo detta macro nell'evento "Workbook_Open" del file con i collegamenti da aggiornare. Nell'esempio che facciamo sotto, gli argomenti li assegniamo scrivendoli direttamente nel codice, ma potremmo reperirli anche da altri dati posti in celle di un foglio del file attivo. Direi che se i collegamenti mirano alla stessa cartella chiusa, sempre quella, conviene inserire gli argomenti direttamente nel codice, ma ognuno agirà come meglio crede. Sotto la funzione PrendiDati():

  • Private Function PrendiDati(percorso, nomefile, foglio, rif)

    Dim arg As String

    ' ci assicuriamo che il file di provenienza dati esista, altrimenti avvisiamo ed usciamo dalla funzione
    If Right(percorso, 1) <> "\" Then percorso = percorso & "\"  'controlliamo anche che nella stringa "percorso" sia stata inserita la barra \
    If Dir(percorso & nomefile) = "" Then
    PrendiDati = "File Non Trovato"
    Exit Function
    End If

    ' usiamo la variabile "arg" assegnando la concatenazione dei valori reperiti con gli argomenti dalla macro "RilevaDati" - questa stringa non occorre modificarla

    arg = "'" & percorso & "[" & nomefile & "]" & foglio & "'!" & Range(rif).Range("A1").Address(, , xlR1C1)
    '
    ' Eseguiamo una XLM macro il cui risultato è assegnato alla funzione stessa
    PrendiDati = ExecuteExcel4Macro(arg)
    End Function

E' nella macro RilevaDati() che useremo il metodo SENDKEYS per inviare a quella benedetta dialogbox citata sopra, che ora ci consente di farlo, sia la password che l'equivalente del tasto "INVIO" ottenendo finalmente la risposta al problemino posto: evitiamo di scrivere noi la password ed otteniamo l'aggiornamento dei dati (senza avere creato un "collegamento")

  • Sub RilevaDati()
    'usiamo 4 variabili come vettori degli argomenti da passare alla funzione, che altro non sono se non il percorso completo che mira al file chiuso e protetto
    percorso = "C:\"
    file = "Cartel1.xls"
    foglio = "Foglio1"
    cella = "A1"
    'impostiamo il tempo di attesa di 1 secondo che compaia la dialogbox (tempo da modificare in funzione della rapidità di risposta del computer: con computer 'moderni, veloci, l'intera riga 'Application.Wait (Now + TimeValue("0:00:1")) può essere omessa inserendo un singolo apice ad inizio istruzione ( ' ))
    Application.Wait (Now + TimeValue("0:00:1"))
    'quindi inviamo la password e la conferma (tasto Invio)
    SendKeys "lui"
    SendKeys "{Enter}"
    'ed infine inviamo ad una cella del foglio attivo, i dati prelevati dal file .xls chiuso, sfruttando la restituzione della funzione PrendiDati
    [B3] = PrendiDati(percorso, file, foglio, cella)
    End Sub

A seguire vediamo una routine che importerà dati da tutta un'area del file .xls chiuso; supponendo che l'area comprenda un'intervallo di più righe e più colonne, si sfrutta un doppio ciclo For.. Next che scorra righe e colonne in tandem, cioè legge dalla cella(rigax, colonnay) sfruttando la funzione PrendiDati() e restituisce detta funzione sul foglio attivo in una cella(rigax, colonnay) , quindi passerà alle successive celle sia in lettura che in scrittura; è ad ogni ciclo che inseriremo le istruzioni SENDKEYS per sproteggere il file .xls chiuso e protetto; ciò vuol dire che per ogni "accesso" svolto dal ciclo è necessario ripetere la password; d'altra parte se non ci piace questa soluzione dovremmo scegliere la soluzione del Workbooks.Open vista all'inizio.

Nell'esempio simuliamo che il file chiuso sia nella cartella Fatturazione, sottocartella Clienti dell'hard-disk C

  • Sub RilevaDati2()
    percorso = "C:\Fatturazione\Clienti"
    file = "Cartel1.xls"
    foglio = "Foglio1"
    Application.ScreenUpdating = False

    For r = 1 To 10        
    'scorriamo le righe dalla 1 alla 10 sul foglio attivo
    For c = 5 To 7         
    'scorriamo le colonne dalla E alla G sul foglio attivo
    cella = Cells(r, c).Address 
    'con la variabile "cella" reperiamo l'indirizzo (Address) della cella scorsa nel ciclo, variabile che forma un argomento della funzione.
    'questa istruzione la possiamo omettere : 'Application.Wait (Now + TimeValue("0:00:1"))
    'quindi inviamo la password e la conferma
    SendKeys "lui"
    SendKeys "{Enter}"
    Cells(r, c) = PrendiDati(percorso, file, foglio, cella)   '
    inviamo ad una cella del foglio attivo, i dati prelevati dal file .xls chiuso, sfruttando la restituzione della 'funzione PrendiDati
    Next c
    Next r
    Application.ScreenUpdating = True
    End Sub

Nell'esempio sopra appare evidente che sia l'area sul foglio attivo (E1:G10 della cartella .xls aperta), sia l'area sul foglio del file .xls chiuso coincidono, ma se dovessimo leggere da un'intervallo diverso da quello su cui scriveremo, dovremo utilizzare incrementi o decrementi sugli indici di riga e di colonna per far coincidere le aree, forse chiariamo meglio con un esempio: area del foglio attivo: A3:C13; area da "collegare" sul file chiuso: E1:G10; condizione necessaria: stesse dimensioni degli intervalli, anche se interessano aree diverse; è necessario modificare gli indici di riga e di colonna delle celle del foglio chiuso nell'istruzione che assegna l'Address alla variabile "cella", ma ovviamente cambieranno anche gli estremi del ciclo che scorre le colonne; vediamo come (solo le varianti):

  • For r = 3 To 13         'scorriamo le righe dalla 3 alla 13 sul foglio attivo
    For c = 1 To 3         
    'scorriamo le colonne dalla A alla C sul foglio attivo
    cella = Cells(r -2, c + 4).Address

infatti, poichè a inizio ciclo "r" equivale a 3 mentre sul foglio chiuso deve iniziare dalla riga 1 della colonna E, togliamo 2 da "r", lo stesso dicasi per "c" che inizialmente equivale a 1 (colonna A) mentre sul file chiuso deve essere la 5 (la colonna E) aggiungiamo 4. Non bisogna confondersi con questi più e meno, basta un pò di calma e di conoscere come si identificano i riferimenti con la sintassi Cells(riga, colonna) (vedi su questo sito).

 

Dati da più cartelle .xls chiuse protette da password.

Un altra ipotesi che possiamo esaminare riguarda la possibilità che sul nostro foglio attivo (come Cartel2.xls dei nostri esempi), risiedano o meglio, esistano "collegamenti" a più cartelle .xls chiuse, diverse tra loro, e tutte protette da password diverse tra loro.

Appare evidente che prima di decidere come impostare l'esecuzione delle istruzioni per le password, dovremo sapere con quale "ordine" excel sente e cerca i vari collegamenti presenti sulla cartella attiva.

Questa verifica ci consentirà di impostare l'ordine con cui invieremo le password per la connessione alle rispettive cartelle xls chiuse.

Excel, indipendentemente dall'ordine di inserimento di un collegamento nel tempo (primo, secondo terzo, ecc) assegna il numero indice dei links in funzione della locazione come riferimento celle. Quindi se per esempio, il quarto collegamento inserito, è stato inserito nella cella A1 (la prima cella del foglio) assumerà l'indice 1 dei collegamenti (e non il 4), e così via secondo la grandezza dell'indice cella.

Possiamo usare una routine veloce per rilevare l'ordine e a quale cartella xls chiusa un certo collegamento mira, e stabilire quindi di quale password necessita; questo routine cerca in tutto l'intervallo del foglio che contiene dati (UsedRange), tutte le celle che contengono formule, legge il secondo carattere della (stringa) formula, e se corrisponde ad un apice ( ' ), ci avvisa con un messaggio indicante in quale cella del foglio attivo e a quale cartella .xls chiusa, completa di percorso,  il collegamento mira:

  • Sub DimmiApice()
    Dim Cell As Range
    For Each Cell In ActiveSheet.UsedRange
    If Cell = "" Then GoTo 10
    collega = Cell.Formula
    If Mid(collega, 2, 1) = "'" Then
    MsgBox "in " & Cell.Address & Chr(13) & collega
    End If
    10:
    Next
    End Sub

Leggeremo quindi la sequenza con cui sono presenti i collegamenti e potremo impostare l'ordine di assegnazione sia delle password, sia dei percorsi che mirano alle cartelle .xls chiuse. Dovremo quindi creare una macro contenente tante istruzioni come quella vista sopra (RilevaDati()), una serie di istruzioni per ogni collegamento, in cui cambieremo i valori delle 4 variabili e relative password, facciamo un esempio con la sequenza a tre cartelle chiuse:

  • Sub RilevaDatiMultipli()
    'usiamo 4 variabili come vettori degli argomenti da passare alla funzione, per il primo dei 3 file chiusi
    percorso = "C:\"
    file = "Cartel1.xls"
    foglio = "Foglio1"
    cella = "A1"
    'quindi inviamo la password e la conferma (tasto Invio)
    SendKeys "lui"
    SendKeys "{Enter}"
    'ed infine inviamo ad una cella del foglio attivo, i dati prelevati dal primo file .xls chiuso
    [B3] = PrendiDati(percorso, file, foglio, cella)

    'argomenti da passare alla funzione, per il secondo dei 3 file chiusi (con "percorso" e password diversi)
    percorso = "C:\Fatture\Clienti"
    file = "TuoFile1.xls"
    foglio = "Foglio3"
    cella = "D8"
    'quindi inviamo la password e la conferma (tasto Invio)
    SendKeys "poi"
    SendKeys "{Enter}"
    'ed infine inviamo ad una cella del foglio attivo, i dati prelevati dal secondo file .xls chiuso
    [B4] = PrendiDati(percorso, file, foglio, cella)

    'argomenti da passare alla funzione, per il terzo dei 3 file chiusi (con "percorso" e password diversi)
    percorso = "C:\Fatture\Fornitori"
    file = "TuoFile2.xls"
    foglio = "Foglio2"
    cella = "C6"
    'quindi inviamo la password e la conferma (tasto Invio)
    SendKeys "gegè"
    SendKeys "{Enter}"
    'ed infine inviamo ad una cella del foglio attivo, i dati prelevati dal terzo file .xls chiuso
    [B5] = PrendiDati(percorso, file, foglio, cella)
    End Sub

Per precisazioni sull'uso della funzione leggete la pagina originale posta al link http://www.j-walk.com/ss/excel/tips/tip82.htm .

 

 

buon lavoro.

prelevato sul sito www.ennius.altervista.org