List and Hyperlink Files in a folder

lonely

Board Regular
Joined
Feb 28, 2003
Messages
230
Hi All,
As usual... again i rammed into a simple problem. I wish to get a listing of all the files in a particular folder which is on my network.

I was able come up with a macro that can actually create this listing along with the "relative path" of the files. However, now my manager is asking me to make this path as active hyperlinks. I dont really have any experience in doing this.

Please can someone help. I have included a sample of my worksheet below. Also, I have included the code of the macro that i use for creating the list of files.

Thanks,
Lonely

**********************
Book1
ABCD
1FileNameFileSizeFileType
2All\final10.ppt585216MicrosoftPowerPointPresentation
3All\final11.ppt246784MicrosoftPowerPointPresentation
4All\final2.ppt616448MicrosoftPowerPointPresentation
5All\final3.ppt585216MicrosoftPowerPointPresentation
6AllProcesses\budg.zip2353312WinZipFile
7AllProcesses\b_plan.ppt196096MicrosoftPowerPointPresentation
8AllProcesses\close.zip1986552WinZipFile
Sheet1

*****************************

Code:
Sub TestListFilesInFolder()
    Workbooks.Add
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File Name:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "Date Last Accessed:"
    Range("F3").Formula = "Date Last Modified:"
    Range("G3").Formula = "Attributes:"
    Range("H3").Formula = "Short File Name:"
    Range("A3:H3").Font.Bold = True
    ListFilesInFolder "C:\Files\", True
End Sub
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

polly

Board Regular
Joined
Apr 5, 2002
Messages
55
How about a formula in D:D, I'm not much on Code :(

=HYPERLINK("C:\"&A5,"Link to Such") enter in D5 and drag down
hyperlink.xls
ABCD
5All\final10.pptLinktoSuch
6All\final11.pptLinktoSuch
7
Sheet1


Polly (y)

PS: For some reason the "\" after the C: isn't in the screen shot
 

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top