Posts by StefanG

StefanG · Jul 29th 2005

Re: add hyperlink to found file list

Hi Norie,
This is just fabulous! Thank you so much for the quick help.
I could not be happier.

I would like to know what the 2 in the code does.
I know that Range("A" is column A
and FoundFiles (I) the stuff it actually found.
Would you mind helping me to understand a little better?

Code

Range("A" & I + 2) = .FoundFiles(I)

Thank you
Stefan

StefanG · Jul 29th 2005

Hi,
this code gathers file location and file names and the output is starting in A3.
I would like to, either
1- have a hyperlink to the respective file in cell B3, B4, etc. or
2- have the searchresult itself, so A3, A4, etc. be the hyperlink

I would prefer option 1 and if possible have the hyperlink text be something like <Get File>.

Thanks for your help.
Stefan

Code

Sub search1()
Dim Message1, Title1, MyValue1
Dim Message2, Title2, MyValue2
Dim Message3, Title3, MyValue3, MyFolder
Range("A:A").ClearContents
Title3 = "Select Search Location"
Message3 = "Perform the search through ..." & Chr(10) & Chr(10) & _
            "1 = ... all Service Forms" & Chr(10) & _
            "2 = ... only Completed Service Forms" & Chr(10) & _
            "3 = ... only Pending Service Forms" & Chr(10)
MyValue3 = InputBox(Message3, Title3)
MyFolder = "C:\_stefan's"
If MyValue3 = "2" Then MyFolder = "C:\_stefan's\_pend"
If MyValue3 = "3" Then MyFolder = "C:\_stefan's\_comp"
'End If
With Application.FileSearch
    .NewSearch
    .LookIn = MyFolder
    .SearchSubFolders = True
Title1 = "Enter Search Term 1"
Title2 = "Enter Search Term 2"
Message1 = "This will perform a search for Service Form files." & Chr(10) & Chr(10) & _
            "You may enter any 'string', i.e. Consultant ID." & Chr(10) & _
            "The output will show the file(s) location, i.e. L:\Consultant Services\Returns\Service_Form\" & Chr(10) & _
            "and the respective file name(s)." & Chr(10)
Message2 = "This will add a search within the file(s)" & Chr(10) & _
            "and can contain wildcards like '*'." & Chr(10) & Chr(10) & _
            "The result(R) has to match the search(S) term exactly." & Chr(10) & _
            "I.e. (S)=Credit or (S)=Cred* will return all files containing (R)Credit whereas (S)=Cred will not be found." & Chr(10)
MyValue1 = InputBox(Message1, Title1)
    If MyValue1 = "" Then
        MsgBox "You have to enter a search term/string.", vbCritical, "No Search Term - Error"
    Exit Sub
    End If
MyValue2 = InputBox(Message2, Title2)
    If MyValue2 = "" Then MyValue2 = ""
    .Filename = MyValue1
    .TextOrProperty = MyValue2
    .FileType = msoFileTypeAllFiles
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
        " file(s) found."
        Range("a3").Select
        For i = 1 To .FoundFiles.Count
        ActiveCell.FormulaR1C1 = .FoundFiles(i)


        ActiveCell.Offset(1, 0).Select
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

Display More

StefanG · Jul 28th 2005

Re: Find Oldest File in Folder

Hi Dave,
Thanks for the quick reply.
The code actually will list all files of the folder and does not return the dates but times. The times returned are all 12:00:00 AM whereas the oldest file date/time is july 13, 2005 9:08.
All the files found are opened and are not being closed.
Any suggestion about modifying the code?
Thank you
StefanG

StefanG · Jul 28th 2005

Re: Find Oldest File in Folder

Hi Dave,
Sorry about that. I hope this looks better now.
StefanG

StefanG · Jul 28th 2005

Hello,
I'd like to be able to incorporate an option into a macro to search through an existing folder (will not change the folder name or location) and check if there are files (*.xls) and if so, return the date (string 1) and the file name (string 2).
This is what i have so far, but i cannot figure out how to find and return the oldest file's info. Also, if the msg would be flexible as to if there are none "0" files in the folder, just return the msgbox "No files in the folder".

Lost.
StefanG

Code

MsgBox "Counting Files"
    With FS
        .LookIn = "c:\test\"
        .FileName = "*.xls"
        If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) > 0 Then
            Range("B2") = FS.FoundFiles.Count
        End If
    End With
MsgBox "There are currently " & _
        FS.FoundFiles.Count & _
        " files in the folder." & _
        "The oldest one is from " ????

Display More