Excel - Modifica massiva degli Hyperlinks

google-plus Scritto da . Postato in Excel

avatar corso excelExcel è un programma avanzato con il quale si possono fare molteplici cose e anche se chi publicizza programmi di contabilità e quant'altro ne denigra in un certo senso l'uso, il fatto che tutti i programmi o quasi consentono l'esportazione di dati in formato xlsx dimostra quanto sia diventato quasi indispensabile.

Tuttavia, nonostante continui progressi e aggiornamenti, come ogni software non è privo di difetti, tanto che in alcuni casi le sue funzioni base non sono sufficienti per riparare i danni causati da alcuni errori o chiusure improvvise del programma.

Nonostante vi sia la possibilità di recuperare i file salvati automaticamente da Excel, in alcuni casi, queste pseudo versioni dei file, non sono complete o sono addiritura modificate e non conformi agli originali.

Questo può essere il caso se vi nei fogli di lavoro utilizzate gli hyperlinks o collegamenti ipertestuali.

Nel mio caso, per comodità ho creato un file con alcune macro registrate che mi consentono di tenere traccia di tutte le fatture in uscita e in ingresso alle quali, inserisco nel campo codice fattura un collegamento ipertestuale per aprire in automatico all'occorrenza il file in pdf corrispondente.

Ciò è molto utile e insieme ai filtri, mi consente in maniera semplice anche se primitiva di svolgere tutte le operazioni di cui ho bisogno.

File recuperati e hyperlinks

Mi è ancora capitato che a causa di blackout, un crash di sistema o di uno spegnimento improvviso del PC, il file venga poi ripresentato nelle due forme: l'originale e il file recuperato. Ho notato che se viene salvato il recuperato, in genere Excel per qualche sorta ti motivo, cambia gli Hyperlinks che ora non puntano più ai file come prima a causa del fatto che il loro percorso nei collegamenti vengono modificati con l'inserimento di un percorso simile C:\users\User\AppData\Roaming\Microsoft\Excel\Documents\Fatture\Fornitore\fattura.pdf

In poche parole, visto che il file recuperato è salvato nelle cartelle temporanee del programma, tutti i riferimenti interni ai collegamenti ipertestuali cambiano di conseguenza venendo sostituiti con il percorso del file, come se anch'essi fossero stati oggetto di qualche spostamento.

Questo è un comportamento alquanto anomalo a mio avviso: un file recuperato non dovrebbe modificare nulla al suo interno consentendo all'utente di recuperare completamente o anche solo in parte il lavoro che altrimenti sarebbe andato perduto.

Nel mio file, gli hyperlinks sono davvero molti, centinaia, forse migliaia. Benché io abbia un sistema di backup, purtroppo in alcuni casi anche questo è fallace, ma anche non lo fosse nel breve termine dei pochi minuti tra un crash imprevisto e il conseguente riavvio, difficile che il sistema di backup automatico possa venire in aiuto.

Ed è qui il problema: perdere tutto il lavoro fatto dopo l'ultimo salvataggio o recuperarlo con la consapevolezza che gli hyperlinks saranno distorti? Io la domanda non ho dovuto pormela visto che dopo un lungo periodo di malattia, qualcuno in ufficio si era preso la briga di rovistare nei miei file con conseguente causa del problema.

Modificare contemporaneamente più collegamenti ipertestuali in Excel

Googolando "modifica massiva hyperlinks excel" o ricerca simile, vi imbatterete in soluzioni basate su diversi approcci. Chi sfruttando le funzioni di Excel e chi invece ha preferito passare dal VBA (Visual Basic for Application).

Sul sito answers.microsoft.com al quesito "Modificare contemporaneamente più collegamenti ipertestuali", si può leggere di un approccio basato sulla funzionalità nascosta di Excel il quale con la combinazione di tasti ALT+F9 mostra i codici di campo (o dovrebbe mostrare), compresa la URL degli hyperlinks. Successivamente, usando la funzione Trova e Sostituisci, secondo il suggerimento esposto, si può modificare tutti i collegamenti ipertestuali delle celle selezionate.

Qualcun'altro, ha invece optato per l'uso di codice VBA per creare una Macro in grado di cambiare tutti i collegamenti ipertestuali nel range di celle interessato. La risposta la si può trovare sempre nel sito answers.microsoft.com al quesito Modifica massiva collegamenti Excel (RISOLTO).

Entrambe le soluzioni non soddisfavano le mie necessità. La prima, perché non riuscivo proprio a fare in modo che Excel mi mostrasse i codici di campo, la seconda perché il codice non sembra del tutto corretto ed inoltre era basato sui reali bisogni di chi l'ha creato.

Macro per la modifica della URL degli hyperlinks nel foglio attivo

Poiché dovevo modificare foglio per foglio tutti i collegamenti ipertestuali contenuti nella cartella di lavoro, ho optato per un approccio sempre basato su VBA ma su di una Macro che prendesse in considerazione non un range limitato ad una o più colonne/righe, ma bensì tutte le celle contenute nel foglio attivo.

L'ispirazione per la soluzione trovata, mi è venuta nel fare ricerche per la manipolazione degli hyperlinks in un foglio Excel: ho trovato l'articolo Excel: come estrarre la URL da una cella nel quale l'autore cerca attraverso una macro di estrarre e manipolare la URL contenuta nelle celle di un determinato range. In seguito ho trovato anche questa spiegazione sul forum ExcelVBA.it dove viene riportata una Macro molto simile a quella qui sotto riportata: Modificare collegamenti ipertestuali.

Poiché nel mio caso, una volta "estratta" la URL ho dovuto modificarla, ho infine usato la funzione Replace in modo da sostituire la parte della URL compromessa prima di sovrascriverla.

Ecco il codice utilizzato:

Sub mod_hyperlinks()

Dim Percorso, Percorso_old, Originale, Corretto As String

Dim h As Hyperlink

Percorso = "NuovoPercorso"

Percorso_old = "AppData/Roaming/Microsoft"

For Each h In ActiveSheet.Hyperlinks

If InStr(h.Address, Percorso_old) <> 0 Then

Originale = h.Address
Corretto = Replace(Originale, Percorso_old, Percorso)
h.Address = Corretto

End If

Next

End Sub

Cosa fa la Macro mod_hyperlinks()

Vediamo in breve cosa fa la macro qui sopra riportata:

La variabile Percorso_old contiene la parte del percorso dell'URL da sostituire nei collegamenti ipertestuali e che verrà ricercato dalla funzione Replace per rimpiazzarla con il contenuto della variabile Percorso.

Percorso = "NuovoPercorso"

Percorso_old = "AppData/Roaming/Microsoft"

Successivamente, viene iniziato il ciclo nel quale per ogni oggetto di tipo Hyperlink trovato nel foglio attivo,

For Each h In ActiveSheet.Hyperlinks

...

Next

verifica che nell'attributo Address del collegamento (la URL) sia contenuta la parte da sostituire.

If InStr(h.Address, Percorso_old) <> 0 Then

...

End If

Se la condizione si verifica, viene estratta la URL dal collegamento ipertestuale, modificata attraverso la funzione Replace e successivamente rimpiazzata la vecchia URL con quella appena modficata.

Originale = h.Address
Corretto = Replace(Originale, Percorso_old, Percorso)
h.Address = Corretto

Dove metto il codice appena visto?

Se non siete avezzi a VBA, per utilizzare questo piccolo snippet di codice, potete farlo andando dal menu Visualizza > Macro > Visualizza Macro > Crea e nella finestra che si apre, incollare il codice sopra riportato.

Se non vedete il Gruppo Macro nella scheda Visualizza, o non riuscite a vedere la scheda Sviluppo, o volete sapere come muovere i primi passi in VBA leggete gli articoli:

Excel - Visualizza Scheda Sviluppo

Usare Excel al meglio con il VBA - Parte 1

VBA sub mod hyperlinks

Considerazioni

Ovviamente, la routine può essere modificata a seconda delle proprie esigenze ed eventualmente, se solo sono state aggiunte cartelle al percorso originale, si può usare la funzione Replace per cancellare la sola parte spuria della path che è stata aggiunta da Microsoft Excel.

Associandola a una combinazione di tasti o prevedendo l'inserimento da tastiera di nuovo e vecchio path (funzione InputBox(...)), si può rendere la procedura ancora più dinamica e funzionale.

Sperando che il mio suggerimento possa essere utile anche a voi, vi invito a commentare o a scrivermi per suggerimenti o altre possibili soluzioni... e non dimenticate di condividere l'articolo!


 

Se hai gradito l'articolo, condividilo, basta un click:

Commenti   

Daniele Gagliardi
0 #3 Daniele Gagliardi 2021-05-07 10:07
Citazione Pasquale:
Mi da un Debug sulla riga
h.Address = Corretto

Ciao Pasquale, Corretto è una variabile: verifica che sia stata riportata correttamente nel codice. Guarda inoltre se semplicemente non hai attivato il debug per quella riga di codice.
Citazione
Pasquale
0 #2 Pasquale 2021-05-06 15:13
Mi da un Debug sulla riga
h.Address = Corretto
Citazione
Pierluigi
+1 #1 Pierluigi 2021-04-18 11:30
Grazie per la procedura che hai condiviso, l'ho utilizzata personalizzandola ed è risultata efficace e funzionale
Citazione

Aggiungi commento

I commenti lasciati dagli utenti del blog sono sempre soggetti a controllo prima di eventuale pubblicazione. I commenti non considerati adatti per contenuti, spam o forma saranno eliminati e non pubblicati. Leggi i Termini e Condizioni di utilizzo per maggiori informazioni.


Codice di sicurezza
Aggiorna

Prima di andartene...

Perché non rimanere in contatto?

Clicca sul pulsante "Mi piace" qui sotto per rimanere aggiornato su tutti i nuovi contenuti e ricevere notifica degli articoli interessanti che condivido giornalmente sulla Pagina Facebook dedicata al blog.

Puoi anche iscriverti alla newsletter e al nuovo canale YouTube di blog.dangerx82 così da rimanere sempre aggiornato sui nuovi contenuti...

x

Per essere più facile ed intuitivo, il blog fa uso dei cookie, piccole porzioni di dati che consentono di capire come gli utenti navighino e ne visualizzino le pagine. I cookie non registrano alcuna informazione personale sull'utente ed eventuali dati identificabili non verranno memorizzati. Ti invitiamo a prendere visione della nostra Informativa sulla Privacy (aggiornata il 25/05/2020). Oppure visiona la nostra Cookie Policy (aggiornata il 17/10/2019):