Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
Assuming it's possible, could someone help me with the macro code to get all
the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
Steve,
Change the .LookIn path as needed.... The list will start in the activecell Sub FindFilesAndHyperlink2Them() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Foler" '.SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).Value = .FoundFiles(i) ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])" Next i Else MsgBox "There were no files found." End If End With End Sub HTH, Bernie MS Excel MVP "Steve_n_KC" wrote in message ... Assuming it's possible, could someone help me with the macro code to get all the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
And if you just want the hyperlink:
Sub FindFilesAndHyperlink2ThemV2() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Folder" ' .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).FormulaR1C1 = "=HYPERLINK(""" & .FoundFiles(i) & """)" Next i Else MsgBox "There were no files found." End If End With End Sub -- HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Steve, Change the .LookIn path as needed.... The list will start in the activecell Sub FindFilesAndHyperlink2Them() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Foler" '.SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).Value = .FoundFiles(i) ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])" Next i Else MsgBox "There were no files found." End If End With End Sub HTH, Bernie MS Excel MVP "Steve_n_KC" wrote in message ... Assuming it's possible, could someone help me with the macro code to get all the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
PERFECT! Thanks So Much!
-- THANKS! Steve "Bernie Deitrick" wrote: Steve, Change the .LookIn path as needed.... The list will start in the activecell Sub FindFilesAndHyperlink2Them() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Foler" '.SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).Value = .FoundFiles(i) ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])" Next i Else MsgBox "There were no files found." End If End With End Sub HTH, Bernie MS Excel MVP "Steve_n_KC" wrote in message ... Assuming it's possible, could someone help me with the macro code to get all the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
Thanks Again! I'm especially appreciative of the inclusion of the message
boxes and the " '.SearchSubFolders = True"...answering questions before they are asked!! Could you tell now tell me if it is possible modify the code to still return the hyperlinks but only display the file name (preferably without the extension)?? When I asked my original question I planned on being able to select "Edit Hyperlink" and assign a vanity name to the link but it isn't a listed option for these cells??? -- THANKS! Steve "Bernie Deitrick" wrote: And if you just want the hyperlink: Sub FindFilesAndHyperlink2ThemV2() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Folder" ' .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).FormulaR1C1 = "=HYPERLINK(""" & .FoundFiles(i) & """)" Next i Else MsgBox "There were no files found." End If End With End Sub -- HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Steve, Change the .LookIn path as needed.... The list will start in the activecell Sub FindFilesAndHyperlink2Them() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Foler" '.SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).Value = .FoundFiles(i) ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])" Next i Else MsgBox "There were no files found." End If End With End Sub HTH, Bernie MS Excel MVP "Steve_n_KC" wrote in message ... Assuming it's possible, could someone help me with the macro code to get all the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
Steve,
Try the macro below. HTH, Bernie MS Excel MVP Sub FindFilesAndHyperlink2ThemV3() Dim i As Integer Dim myF As String With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Folder" ' .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count myF = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1) myF = Left(myF, Len(myF) - 4) ActiveCell.Cells(i, 1).FormulaR1C1 = _ "=HYPERLINK(""" & .FoundFiles(i) & """,""Click to open " & myF & """)" Next i Else MsgBox "There were no files found." End If End With End Sub "Steve_n_KC" wrote in message ... Thanks Again! I'm especially appreciative of the inclusion of the message boxes and the " '.SearchSubFolders = True"...answering questions before they are asked!! Could you tell now tell me if it is possible modify the code to still return the hyperlinks but only display the file name (preferably without the extension)?? When I asked my original question I planned on being able to select "Edit Hyperlink" and assign a vanity name to the link but it isn't a listed option for these cells??? -- THANKS! Steve "Bernie Deitrick" wrote: And if you just want the hyperlink: Sub FindFilesAndHyperlink2ThemV2() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Folder" ' .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).FormulaR1C1 = "=HYPERLINK(""" & .FoundFiles(i) & """)" Next i Else MsgBox "There were no files found." End If End With End Sub -- HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Steve, Change the .LookIn path as needed.... The list will start in the activecell Sub FindFilesAndHyperlink2Them() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Foler" '.SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).Value = .FoundFiles(i) ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])" Next i Else MsgBox "There were no files found." End If End With End Sub HTH, Bernie MS Excel MVP "Steve_n_KC" wrote in message ... Assuming it's possible, could someone help me with the macro code to get all the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
hyperlink to files
You may get tired of hearing it but...Works PERFECT! 3 for 3!!!
-- THANKS! Steve "Bernie Deitrick" wrote: Steve, Try the macro below. HTH, Bernie MS Excel MVP Sub FindFilesAndHyperlink2ThemV3() Dim i As Integer Dim myF As String With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Folder" ' .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count myF = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1) myF = Left(myF, Len(myF) - 4) ActiveCell.Cells(i, 1).FormulaR1C1 = _ "=HYPERLINK(""" & .FoundFiles(i) & """,""Click to open " & myF & """)" Next i Else MsgBox "There were no files found." End If End With End Sub "Steve_n_KC" wrote in message ... Thanks Again! I'm especially appreciative of the inclusion of the message boxes and the " '.SearchSubFolders = True"...answering questions before they are asked!! Could you tell now tell me if it is possible modify the code to still return the hyperlinks but only display the file name (preferably without the extension)?? When I asked my original question I planned on being able to select "Edit Hyperlink" and assign a vanity name to the link but it isn't a listed option for these cells??? -- THANKS! Steve "Bernie Deitrick" wrote: And if you just want the hyperlink: Sub FindFilesAndHyperlink2ThemV2() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Folder" ' .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).FormulaR1C1 = "=HYPERLINK(""" & .FoundFiles(i) & """)" Next i Else MsgBox "There were no files found." End If End With End Sub -- HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Steve, Change the .LookIn path as needed.... The list will start in the activecell Sub FindFilesAndHyperlink2Them() Dim i As Integer With Application.FileSearch .NewSearch .LookIn = "C:\Documents and Settings\Foldername\My Foler" '.SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count ActiveCell.Cells(i, 1).Value = .FoundFiles(i) ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])" Next i Else MsgBox "There were no files found." End If End With End Sub HTH, Bernie MS Excel MVP "Steve_n_KC" wrote in message ... Assuming it's possible, could someone help me with the macro code to get all the file names, regardless of type, within a specific folder and then list those names including as much as possible of the file path in a column of the sheet that I execute the macro from? If the macro can pull the info in as a hyperlink that would be a bonus but I can work that separately if needed. I need this to be something that I can assign to a button so that the user can update the list prior to each use. -- THANKS! Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink files same folder only? | Excel Discussion (Misc queries) | |||
hyperlink to files? | Excel Discussion (Misc queries) | |||
I can't open PDF files when using the hyperlink | Excel Worksheet Functions | |||
hyperlink to files | Excel Worksheet Functions | |||
hyperlink to jpg files | Excel Discussion (Misc queries) |