|
|
|
|
|
|
Excel
|
Hyperlink list of modified excel workbooks
|
|
Ease of Use
|
Easy
|
Version tested with
|
2003
|
Submitted by:
|
CBrine
|
Description:
|
The process will add a hyperlinked list of all files that have been modified within the last N days to the activeworkbook.
|
Discussion:
|
Use this code to determine if any excel files within a folder have been modified within a certain period of time. It creates a handy hyperlink to each modified file, so you can easily open them.
The process uses the microsoft scripting runtime dll(scrrun.dll), so this must be registered, but does not need to be referenced since I use late binding.
|
Code:
|
instructions for use
|
Option Explicit
Sub ListModifiedFiles()
Dim f As Object, fso As Object
Dim folder As String
Dim DayRange As Integer
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set fso = CreateObject("Scripting.FileSystemObject")
DayRange = 7
ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp)).Delete
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancel Selected or no files found that meet criteria"
End
End If
folder = .SelectedItems(1)
End With
With ws.Range("A1")
.Value = "List of workbooks modified within the last " & DayRange & " days"
.Interior.ColorIndex = 15
.Font.Bold = True
End With
ws.Range("A:A").EntireColumn.AutoFit
For Each f In fso.GetFolder(folder).Files
If f.DateLastModified > Now() - DayRange And f.Type = "Microsoft Excel Worksheet" Then
ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0) = "=hyperlink(""" & f.Path & """,""" & f.ShortName & """)"
End If
Next
End Sub
|
How to use:
|
- Open a new workbook
- Press Alt F-11 to open the VBE editor
- Using the project explorer, Double-click on the worksheet you wish to run the code on
- Paste the code to the VBE code window
- Close the VBE
- Activate your the selected worksheet
- Press Alt F8 and select and run the ListModifiedFiles Macro
|
Test the code:
|
- When macro is executed a folder selector dialog should appear
- If cancel is pressed or no files meeting criteria are found process will end
- A hyperlinked list of all files meeting your criteria should appear in column A.
|
Sample File:
|
Create Hyperlink based on modified date.zip 13.59KB
|
Approved by mdmackillop
|
This entry has been viewed 169 times.
|
|