Skip to main content

 Conas gach ainm comhaid a liostáil i bhfillteán agus hipearnasc a chruthú dóibh in Excel?

Maidir le d’obair laethúil, b’fhéidir go mbeidh ort ainmneacha comhaid iolracha a iompórtáil ó fhillteán go bileog oibre, agus hipearnasc a chruthú do gach comhad ionas go n-osclófar an comhad go tapa agus go héasca. B’fhéidir, is féidir leat na hainmneacha comhaid a chóipeáil agus a ghreamú agus hipearnasc a chruthú dó ceann ar cheann de láimh, ach, cuirfidh sé sin amú go leor ama má tá na céadta comhad san fhillteán. Labhróidh mé inniu faoi roinnt modhanna tapa agus suimiúla chun déileáil leis an bpost seo.

Liostaigh gach ainm comhaid i bhfillteán agus cruthaigh hipearnasc le cód VBA

Liostaigh gach ainm comhaid i bhfillteán agus cruthaigh hipearnasc le Kutools le haghaidh Excel


Chun hipearnasc a liostáil agus a chruthú le comhaid ó fhillteán, is féidir leis an gcód VBA seo a leanas fabhar a thabhairt duit.

1. Gníomhaigh bileog oibre nua chun an toradh a chur.

2. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

3. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

VBA: Liostaigh gach ainm comhaid i bhfillteán agus cruthaigh hipearnasc

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. Ansin brúigh F5 eochair chun an cód seo a rith, agus roghnaigh an fillteán a theastaíonn uait na comhaid a liostáil ón Brabhsáil fuinneog, féach an scáileán:

liosta doc cruthaigh hipearnasc 1

5. Tar éis duit an fillteán a shonrú, cliceáil OK cnaipe, agus tá na comhaid go léir san fhillteán sonrach liostaithe i mbileog oibre gníomhach le hipearnasc, féach an scáileán:

liosta doc cruthaigh hipearnasc 2

nótaí: Má tá fofhillteáin i d’fhillteán ar leith, ní liostálfar na comhaid sna fofhillteáin.


Chun gach comhad a liostáil i bhfillteán agus ina fho-fhillteáin agus hipearnasc cruthaithe do gach comhad, Kutools le haghaidh Excel'S Liosta Ainm comhaid féadfaidh tú fabhar a dhéanamh. Leis an ngné seo, liostálfar gach comhad san fhillteán agus sna fofhillteáin ag an am céanna le roinnt tréithe, mar shampla ainm comhaid, méid comhaid, am cruthaithe, fillteán agus mar sin de.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá.

Má tá tú suiteáilte agat Kutools le haghaidh Excel, déan na céimeanna seo a leanas le do thoil:

1. Cliceáil Kutools Plus > Iompórtáil & Easpórtáil > Liosta Ainm comhaid, féach ar an scáileán:

2. sa Liosta Ainm comhaid bosca dialóige, déan na hoibríochtaí seo a leanas le do thoil:

(1.) Cliceáil liosta doc cruthaigh hipearnasc 5 cnaipe chun an fillteán a theastaíonn uait ainmneacha na gcomhad a liostáil a roghnú;

(2.) Seiceáil Cuir comhaid san áireamh i bhfochomhadlann más mian leat ainmneacha comhaid a liostáil i bhfofhillteáin, seiceáil Cuir comhaid agus fillteáin i bhfolach san áireamh más mian leat na comhaid fholaithe a liostáil;

(3.) Sonraigh na cineálacha comhaid is mian leat a fháil agus liostáil faoin Cineál comhaid alt;

(4.) Roghnaigh an t-aonad méid comhaid is mian leat a thaispeáint;

(5.) Seiceáil Cruthaigh hipearnasc ag bun na láimhe clé den bhosca dialóige.

Íoslódáil Kutools le haghaidh Excel Anois!

3. Ansin cliceáil Ok cnaipe, agus tá na comhaid go léir san fhillteán agus sna fofhillteáin liostaithe i mbileog oibre nua leis na hipearnasc de réir mar a theastaíonn uait, féach an scáileán:

liosta doc cruthaigh hipearnasc 6

Cliceáil chun níos mó sonraí a fháil faoin bhfóntas Liosta Ainm Comhad ...

Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!


Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Airteagal gaolmhar:

Conas gach comhad i bhfillteán agus i bhfofhillteáin a liostáil i mbileog oibre?

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (17)
No ratings yet. Be the first to rate!
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
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!
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!
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
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 :)
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?
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
This comment was minimized by the moderator on the site
How would you code this to make hyperlinks for path and directory
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
This comment was minimized by the moderator on the site
How do you display also the files. Not just folders and subfolders?
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!
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 :)
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 :)
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.
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.
This comment was minimized by the moderator on the site
Awesome ..thanks a lot. It really saved my time.
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.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations