Vai al contenuto principale

 Come elencare tutti i nomi di file in una cartella e creare collegamenti ipertestuali per loro in Excel?

Per il tuo lavoro quotidiano, potresti dover importare più nomi di file da una cartella a un foglio di lavoro e creare collegamenti ipertestuali per ogni file in modo da aprire il file rapidamente e facilmente. Forse, puoi copiare e incollare i nomi dei file e creare un collegamento ipertestuale uno per uno manualmente, ma questo farà perdere molto tempo se ci sono centinaia di file nella cartella. Oggi parlerò di alcuni metodi rapidi e interessanti per affrontare questo lavoro.

Elenca tutti i nomi di file in una cartella e crea collegamenti ipertestuali con codice VBA

Elenca tutti i nomi di file in una cartella e crea collegamenti ipertestuali con Kutools per Excel

Per elencare e creare collegamenti ipertestuali con file da una cartella, il seguente codice VBA può farti un favore.

1. Attiva un nuovo foglio di lavoro per inserire il risultato.

2. Tieni premuto il ALT + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

3. Clic inserire > Modulie incolla il codice seguente nel file Moduli Finestra.

VBA: elenca tutti i nomi di file in una cartella e crea collegamenti ipertestuali

Sub Example1()
'Updateby Extendoffice
    Dim xFSO As Object 
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

4. Quindi premere F5 chiave per eseguire questo codice e scegliere la cartella in cui si desidera elencare i file dal Scopri la nostra gamma di prodotti finestra, vedi screenshot:

elenco documenti creare collegamenti ipertestuali 1

5. Dopo aver specificato la cartella, fare clic su OK pulsante e tutti i file nella cartella specifica sono stati elencati nel foglio di lavoro attivo con collegamenti ipertestuali, vedi screenshot:

elenco documenti creare collegamenti ipertestuali 2

Note:: Se sono presenti sottocartelle nella cartella specifica, i file nelle sottocartelle non verranno elencati.

Per elencare tutti i file sia in una cartella che nelle sue sottocartelle e creare collegamenti ipertestuali per ogni file, Kutools for Excel'S Elenco nomi file potrebbe farti un favore. Con questa funzione, tutti i file nella cartella e nelle sottocartelle verranno elencati contemporaneamente con alcuni attributi, come il nome del file, la dimensione del file, l'ora di creazione, la cartella contenente e così via.

Kutools for Excel : con oltre 300 utili componenti aggiuntivi di Excel, da provare gratuitamente senza limitazioni in 30 giorni.
Vai a Download
Prova gratuita di 30 giorniAcquista
PayPal / Il mio commercio

Se hai installato Kutools for Excel, eseguire i seguenti passaggi:

1. Clic Kutools Plus > Importa ed esporta > Elenco nomi file, vedi screenshot:

2. Nel Elenco nomi file finestra di dialogo, eseguire le seguenti operazioni:

(1.) Fare clic su elenco documenti creare collegamenti ipertestuali 5 pulsante per selezionare la cartella in cui si desidera elencare i nomi dei file;

(2.) Controllare Includi file nelle sottodirectory se vuoi elencare i nomi dei file all'interno delle sottocartelle, seleziona Includi file e cartelle nascosti se vuoi elencare i file nascosti;

(3.) Specificare i tipi di file che si desidera trovare ed elencare sotto Tipo di file sezione;

(4.) Selezionare l'unità di dimensione del file che si desidera visualizzare;

(5.) Controllare Crea collegamenti ipertestuali nella parte inferiore sinistra della finestra di dialogo.

Scarica Kutools per Excel adesso!

3. Quindi fare clic Ok pulsante e tutti i file nella cartella e nelle sottocartelle sono stati elencati in un nuovo foglio di lavoro con i collegamenti ipertestuali di cui hai bisogno, vedi screenshot:

elenco documenti creare collegamenti ipertestuali 6

Fare clic per conoscere ulteriori dettagli sull'utilità Elenco nomi file ...

Scarica e prova gratuitamente Kutools per Excel ora!

Kutools for Excel: con più di 300 utili componenti aggiuntivi di Excel, liberi di provare senza limitazioni in 30 giorni. Scarica e prova gratuita ora!

Articolo correlato:

Come elencare tutti i file nella cartella e nelle sottocartelle in un foglio di lavoro?

I migliori strumenti per la produttività in ufficio

🤖 Assistente AI di Kutools: Rivoluziona l'analisi dei dati basandosi su: Esecuzione intelligente   |  Genera codice  |  Crea formule personalizzate  |  Analizzare i dati e generare grafici  |  Richiama le funzioni di Kutools...
Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli  |   VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule   |  Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle   |  Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti:  12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   50+ Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

Descrizione

Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
Comments (17)
No ratings yet. Be the first to rate!
Diego
Diego
This comment was minimized by the moderator on the site
How can you change cell and column location? as I have data already in column A, I would like to change the macro to be in column N
Reply
0
0
skyyang
skyyang     Diego
This comment was minimized by the moderator on the site
Hello, Diego,
To put the result in Column N, please apply the below code:
Sub Example1()
'Updateby Extendoffice
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 14), xFile.Path, , , xFile.Name
    Next
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
DuffBoy
DuffBoy
This comment was minimized by the moderator on the site
Hallo zusammen,

ich habe eine bescheidene Frage:
Wie kann ich Eine Liste von Dateinamen in klickbare Links umwandeln query.
Es sind hier Beispiele genannt worden. Dies hat mich aber nicht weiter gebracht.
Hat wer eine Beispieldatei? Wie aus dem Bericht #28151.

Danke!
Reply
0
0
MandyZhou
MandyZhou     DuffBoy
This comment was minimized by the moderator on the site
Hello friend,

After all the files in the specific folder have been listed in the active worksheet with hyperlinks, you can click the cells to open the hyperlinks. All the filenames are clickable. Please see the screenshots.

But I can't send you my example Excel file, because those hyperlinks listed in the file can only be opened from my own computers. Just use the method to create hyperlinks with files from your specific folder. After the filenames with hyperlinks are listed in a new file. Just click the hyperlinks to open them. Please have a try.

Sincerely,
Mandy
View Attachments (4 / 5)
Reply
Report
0
0
SerajB
SerajB
This comment was minimized by the moderator on the site
@skyyang Hi,
Could this code be edited to hyperlink path for files instead of folders. For example, instead of hyperlinking the paths of the folders inside Folder1, it would hyperlink the paths of all files in Folder1 (i need the text to display as the path of the file, not the file name), that would be the perfect use case for me.
appreciate any help you could offer :)
Reply
Report
0
0
Ellie
Ellie
This comment was minimized by the moderator on the site
How would I change this code to have it list folder names rather than file names? And how would I change it to check subfolders for files?
Reply
0
0
skyyang
skyyang     Ellie
This comment was minimized by the moderator on the site
Hi, Ellie,
To list all folder and subfolder names from a directory, the below VBA code can help you, please try, hopt it can help you.

Sub FolderNames()
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
xRow = Range("A1").End(xlDown).Row + 1
Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
Next SubFolder
For Each subfld In prntfld.SubFolders
getSubFolder subfld
Next subfld
End Sub
Reply
Report
0
0
yuri
yuri     skyyang
This comment was minimized by the moderator on the site
How would you code this to make hyperlinks for path and directory
Reply
0
0
skyyang
skyyang     yuri
This comment was minimized by the moderator on the site
Hi, yuri,
To solve your problem, please try the following code:

Sub FolderNames()
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim xRg As Range
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
Set xRg = xWs.Cells(1, 1)
xRg.Value = xPath
xWs.Hyperlinks.Add Anchor:=xRg, Address:=xPath, TextToDisplay:=xPath
xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.GetFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
Dim xStr As String
Dim xRg As Range
For Each SubFolder In prntfld.SubFolders
xRow = Range("A1").End(xlDown).Row + 1
Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
Set xRg = Cells(xRow, 1)
xRg.Worksheet.Hyperlinks.Add Anchor:=xRg, Address:=xRg.Value, TextToDisplay:=xRg.Value
Set xRg = Cells(xRow, 2)
xRg.Worksheet.Hyperlinks.Add Anchor:=xRg, Address:=xRg.Value, TextToDisplay:=xRg.Value
Next SubFolder
For Each subfld In prntfld.SubFolders
getSubFolder subfld
Next subfld
End Sub
Reply
Report
0
0
isabel
isabel     skyyang
This comment was minimized by the moderator on the site
How do you display also the files. Not just folders and subfolders?
Reply
0
0
skyyang
skyyang     isabel
This comment was minimized by the moderator on the site
Hello, isabel,
To list file folder and file names, the following code may help you:
Option Explicit
Sub ListFoldersAndFilesWithDialog()
    Dim fso As Object
    Dim mainFolder As Object
    Dim ws As Worksheet
    Dim rowIndex As Long
    Dim folderPath As String
    ' Open folder picker dialog to select main folder path
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            folderPath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set mainFolder = fso.GetFolder(folderPath) 
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("FolderList")
    On Error GoTo 0  
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = "FolderList"
    End If
    ws.Cells.Clear
    rowIndex = 1
    ws.Cells(rowIndex, 1).Value = "Folder Name"
    ws.Cells(rowIndex, 2).Value = "File Name"
    ListFilesInFolder mainFolder, ws, rowIndex + 1
End Sub

Sub ListFilesInFolder(ByVal folder As Object, ByVal ws As Worksheet, ByRef rowIndex As Long)
    Dim file As Object
    Dim subFolder As Object
    Dim linkAddress As String  
    For Each file In folder.Files
        ws.Cells(rowIndex, 1).Value = folder.Name
        ws.Cells(rowIndex, 2).Value = file.Name
        linkAddress = file.Path
        ws.Hyperlinks.Add Anchor:=ws.Cells(rowIndex, 2), Address:=linkAddress
        rowIndex = rowIndex + 1
    Next file
    
    For Each subFolder In folder.SubFolders
        ListFilesInSubFolder subFolder, ws, rowIndex
    Next subFolder
End Sub

Sub ListFilesInSubFolder(ByVal subFolder As Object, ByVal ws As Worksheet, ByRef rowIndex As Long)
    Dim file As Object
    Dim linkAddress As String 
    For Each file In subFolder.Files
        ws.Cells(rowIndex, 1).Value = subFolder.Name
        ws.Cells(rowIndex, 2).Value = file.Name
        linkAddress = file.Path
        ws.Hyperlinks.Add Anchor:=ws.Cells(rowIndex, 2), Address:=linkAddress
        rowIndex = rowIndex + 1
    Next file
    Dim subSubFolder As Object
    For Each subSubFolder In subFolder.SubFolders
        ListFilesInSubFolder subSubFolder, ws, rowIndex
    Next subSubFolder
End Sub


After copying and pasting the code, please click Tools > References, and then choose Microsoft Scripting Runtime from the Reference-VBAproject dialog box.
https://cdn.extendoffice.com/images/stories/comments/comment-skyyang/2023-comment/doc-list-files.png

And then, run the code.

Also, you can use our Kutools for Excel's Filename List feature to solve this job quickly and easily.


Please have a try, hope it can help you!
Report
0
0
SerajB
SerajB     skyyang
This comment was minimized by the moderator on the site
@skyyang Hi,
Could this code be edited to hyperlink path for files instead of folders. For example, instead of hyperlinking the paths of the folders inside Folder1, it would hyperlink the paths of all files in Folder1 (i need the text to display as the path of the file, not the file name), that would be the perfect use case for me.
appreciate any help you could offer :)
Reply
Report
0
0
SerajB
SerajB     skyyang
This comment was minimized by the moderator on the site
@skyyang Hi,Could this code be edited to hyperlink path for files instead of folders. For example, instead of hyperlinking the paths of the folders inside Folder1, it would hyperlink the paths of all files in Folder1 (i need the text to display as the path of the file, not the file name), that would be the perfect use case for me. appreciate any help you could offer :)
Reply
0
0
jms
jms
This comment was minimized by the moderator on the site
"VBA: List all filenames in a folder and create hyperlinks" is great! It works perfectly! Thank you for posting! One question... Now that you told us how to add links from folder, is it possible to use those links to get information from say one or two different sheets in each of the files listed? They would be the same cells for each of the files we now have a list for. This would help create a comprehensive contents with active links to otherwise unintelligible file names.
Reply
0
0
alice
alice
This comment was minimized by the moderator on the site
Great thanks although can not view the files in the selected directory as barry said, it creates hyperlinks for all files in that directory anyway.
Reply
0
0
mubashirazizch@gmail.com
This comment was minimized by the moderator on the site
Awesome ..thanks a lot. It really saved my time.
Reply
Report
0
0
barry
barry
This comment was minimized by the moderator on the site
Running the VBA code listed above, shows the selected directory is empty, when it is full of Excel files.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations