Excel 2003 Macro

[imported_cinder]

Can I create a macro that would allow me to create hyperlinks to every file in a single folder? I would have an individual link for every file.

I have a spread sheet I am working on for work and there must be over a thousand links that I need to create. If I can make a macro to take those files to create the links for me then it would be a HUGE time saver.

FYI, I am no expert at programing.

Thanks in advance for any help.

 

[mayest]

This code should get you pretty close:

http://en.allexperts.com/q/Exc...A-Help-Create-List.htm

That will get you all of the Excel file names, but you'll need to get the entire path and create the hyperlink() function. Note, I'm pretty sure (99%) that the call to the Left function should be changed to Right instead, because you want to make sure that there is a slash on the right end of the directory name. I hesitate to say that for sure (100%) only because the code was provided by an Excel MVP. Still, they make mistakes just like the rest of us.

 

[imported_cinder]

That is EXACTLY what I need but I still do not know how to add hyperlinks. Like I said I'm no expert programmer.

I also need to add links to word files too. Any tips would be greatly appreciated. In the mean time I will try to come up with the hyperlink code on my own but if you list it for me that would be fantastic.

 

[imported_cinder]

I found the following code that does almost what I need. I need to add the hyperlinks that will take me to that file. Thanks in advnace for any help provided!!

Sub ListAllFiles()
Dim fs As FileSearch, ws As Worksheet, i As Long
Set fs = Application.FileSearch
With fs
.SearchSubFolders = False ' set to true if you want sub-folders included
.FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
.LookIn = "C:\" 'modify this to where you want to serach
If .Execute > 0 Then
Set ws = Worksheets.Add
For i = 1 To .FoundFiles.Count
ws. Cells(i, 1) = Mid$(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
Next
Else
MsgBox "No files found"
End If
End With
End Sub

 

[mayest]

To add the Hyperlink() function to a cell, replace the "ws.cells(..." line in the For loop with this:

ws.Cells(i, 1).Formula = "=hyperlink(" & Chr(34) & .FoundFiles(i) & Chr(34) & "," & Chr(34) & "Some Text" & Chr(34) & ")"

You will want to replace the "Some Text" with whatever text you want. For example, to show the file name use this:

ws.Cells(i, 1).Formula = "=hyperlink(" & Chr(34) & .FoundFiles(i) & Chr(34) & "," & Chr(34) & Mid$(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1) & Chr(34) & ")"

Note, though, that your code will not work in Excel 2007 due to changes in the object model.

 

[imported_cinder]

Got it all working.. thanks for all your help!