Solved: Browse A Folder And Hyperlink All Modified WBs

[Wolfgang]

Hi All...

May I have a macro which will perform the following tasks, please...

Browse through a folder plus its subs, find all modified .xls files, list them in the active workbook and hyperlink them, so that I am able to open a particular wb from here...

Thank you very much and: Stay On The Scene...

Best,
Wolfgang

[CBrine]

all modified .xls files,

Wolfgang,
Can you expand on what you mean by this? Setting up code that would hyperlink all excel files in a folder wouldn't be that difficult. Doing this based on it being modified? That's another story, and depends on what you mean by modified.

Cal

[lucas]

See Ken's kb entry for a start:
Create Hyperlinked List of Directory

[lucas]

Ken's entry includes last modified date.....should be able to apply a filter to that data.

[Wolfgang]

Hi Cal...

Thank you very much for your quick reply...

Of course, I forgot to mention the most important part...

By modified I mean all files that have been modified from TODAY() minus 7 days...

Best,
Wolfgang

[Wolfgang]

Hi Lucas...

I was tooooo quick with the buttons...

Ken's macro gives me by far more than I needed...thank you very much and have a great weekend...

Best,
Wolfgang

[CBrine]

Wolfgang,
I put some code together for you, so I'm going to post it anyway. If you figured it out from the KB, no worries.

[vba]
sub FileLink()
Dim f As Object, fso As Object
Dim folder As String
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancel Selected"
End
End If

End With

For Each f In fso.GetFolder(folder).Files
If f.DateLastModified > Now() - 7 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
[/vba]

HTH
Cal

PS-Slight change on the error checking.

[Wolfgang]

Hi Cal...

Thank you too for your answer...

I put your code into a module and ran it...

Looks like it does not accept a folder although the folder menu appears ok...

If I try to select one its name does not appear in the menu bar and the code stops...

Any idea?!

Best,
Wolfgang

[CBrine]

Wolfgang,
I've uploaded the test file I created. Give that a try and see if you encounter the same issue.

Cal

[Wolfgang]

Hi Cal...

Still the same..

I get the following error message:

Run-time error 5
Invalid procedure or call argument

For Each f In fso.GetFolder(folder).Files is highlighted...

Best,
Wolfgang

[Wolfgang]

PS:

I am running US Office 2003 Pro SP2 on US XP-Home Edition...

[lucas]

Hi Wolfgang,
haven't had time to look at why.....doesn't appear to be a reference but I get the same error.

[CBrine]

Wolfgang,
When I fixed the error checking, I broke the folder select. Didn't test after I made the change. here's the fixed code.

[VBA]
Sub FileLink()
Dim f As Object, fso As Object
Dim folder As String
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancel Selected"
End
End If
folder = .SelectedItems(1)
End With
For Each f In fso.GetFolder(folder).Files
If f.DateLastModified > Now() - 7 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
[/VBA]

[lucas]

Something still awry Cal.....its not listing the files in the browse dialog and now I don't get an error but nothing happens.

[Wolfgang]

That's it Cal...

Thanxs a lot...runs great...

BTW: I love Ontario...I practically grew up in Phoenix?!

...have a beer on me...

Wolfgang

[CBrine]

lucas,
The browse dialog is actually a folder selector, not a file selector, so no files should appear. You use it to select the folder, and the process will cycle through each excel file that's been modified in the last 7 days and then creates a hyperlink on the activesheet.

HTH
Cal

Wolfgang,
Glad it's working for you. I'm not sure I get the reference about Ontario and Phoenix though? Maybe your thinking of some other Ontario. I will have the beer though.:-)

[CBrine]

lucas,

PS-Nothing will happen if you select a folder where nothing has been modified in the last 7 days or has no excel files.

[Wolfgang]

Cal...

I am from Bavaria living near Munich...

Us Bavarians are world-wide known for our weird kind of humor...I sure am no exception to the rule...no offence Cal...

But we do are proud of our beer......yeah...

Take care...
Wolfgang

[lucas]

Gotcha Cal....works great after I modified an old excel file.....

Would make a nice kb entry......would you submit it?

[CBrine]

Sure, I will have to do some reasearch into how to go about submitting it though.

Cal