Finding from till end

[Airborne]

Hi all. Last time I got great help from Ken about a search engine. Now after some days of using the engine some requests arose. They want to put a date in the form and then search for files and put the files in a sheet as hyperlinks. The problem is that the names of the file are Datadd-mm-yyyy_1_morning, Datadd-mm-yyyy_1_afternoon, Datadd-mm-yyyy_1_night, Datadd-mm-yyyy_2_morning, Datadd-mm-yyyy_2_afternoon, etc. Now for the search only the mm-dd-yyyy are important since they only want to search for instance for files from 10-11-2004 till 22-11-2004 (22-11-2004 being the last day in the Nov-2004 map at that moment). Only search per month.

I want to use the same form and codes as Ken made but they have to be adjusted a bit . I'll upload the form and here are the codes

Option Explicit
Private Sub cmdOkay2_Click()
Const MainPath = "\\Disk1\Data\Year\"
Dim Prompt As String, _
FilesToProcess As Integer, _
fso As Object, _
FullFilePath As String
'Make sure both textboxes have values assigned
If Not IsDate(tbDate2.Value) Then Prompt = "Please enter a prompt date" & vbCrLf
If tbSearch2.Value = "" Then Prompt = Prompt & "Please enter something to search for"
'If Prompt is empty, then no problems were detected
If Prompt = "" Then
'Create a file scripting object and set the FullFilePath variable
Set fso = CreateObject("Scripting.FileSystemObject")
FullFilePath = MainPath & Year(tbDate2) & "" & Format(tbDate2, "d-m")
'Check if the file path exists, and count the number of files in it
If fso.FolderExists(FullFilePath) Then
FilesToProcess = fso.GetFolder(FullFilePath).Files.Count
If FilesToProcess > 0 Then
'If folder for the month exists & has files in it, call FindAll routine
Call FindAll2(FullFilePath, tbSearch1.Value, FilesToProcess)
Unload Me
Else
'If no files are in the directory, inform the user
MsgBox "The information you entered generated a file path of:" & _
vbCrLf & MainPath & Year(tbDate2) & "" & Format(tbDate2, "mmm") & _
vbCrLf & vbCrLf & "There are no files in that directory!" & vbCrLf & _
"Please modify your selection and try again!", _
vbOKOnly + vbCritical, "Directory is empty!"
End If
Else
'If the folder for the month does not exist, notify the user
MsgBox "The information you entered generated a file path of:" & vbCrLf & _
MainPath & Year(tbDate2) & "" & Format(tbDate2, "mmm") & vbCrLf & vbCrLf & _
"That file path does not exist! Please modify your selection and try again!", _
vbOKOnly + vbCritical, "Folder does not exist!"
End If
Else
'If Prompt is not empty, tell the user what info need correcting and return to the
'userform
MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
vbCritical + vbOKOnly, "Please try again!"
End If
End Sub

Private Sub cmdCancel2_Click()
'Unload the userform
Unload Me
End Sub

Private Sub UserForm_Initialize()
'Shrink userform so progress bar doesn't show
Me.Height = 108
'Put today's date in the userform
tbDate2.Value = Format(Now(), "mmm-yyyy")
End Sub

Thanks.

[Ken Puls]

Hi again, Airborne!

So, just to clarify here... your file names look like this?

Data22-11-2004_1_morning.xls
Data22-11-2004_1_afternoon.xls
Data22-11-2004_1_evening.xls
Data22-11-2004_2_morning.xls
Data22-11-2004_2_afternoon.xls
Data22-11-2004_2_evening.xls

Is that correct? If not, can you post a two day sample of the file names you might get?

Cheers,

[Airborne]

Hi Ken ,

There are 5 shifts. Shift 1,2,3,4,5 and they all cover morning/afternoon/night. So in one month you will see for instance on the first of november Data1-11-2004_2_night, Data1-11-2004_1_morning, Data1-11-2004_4_afternoon.
If we want to see the reports of the 1st of november we are only interested in the reports and not the shift and name of the shift.

Now when someone comes back from a week off on say the 15th, he wants to look at the reports of the 1st up till the 15th. But he must also have the choice to only open reports from say the 10th up till the 15th.

I thought of using your form where I type the month-year in one textbox and the day from-till in the other box. When I enter the date's it will open the reports of the dayrange and show them as hyperlinks on sheet Found.

Thanks and regards.

[Ken Puls]

Hi Airborne,

I'm not completely clear on what we're trying to get to here... Are we still searching each file for a specific term in a text box?

If we receive the following list:
Data22-11-2004_1_morning.xls
Data22-11-2004_1_afternoon.xls
Data22-11-2004_1_evening.xls
Data22-11-2004_2_morning.xls
Data22-11-2004_2_afternoon.xls
Data22-11-2004_2_evening.xls

What output are we looking for... just Data22-11-2004? Are we trying to pull all the shifts into one file? Wen someone clicks on the hyperlink, what file should it open?

Sorry, I'm all questions here...

[Airborne]

Sorry Ken for not being clear enough. No we are not searching for text anymore. The search option is great, no problems. I guess it's much easier (for you ). I just want to put files of a desired date range (e.g. the reports of the 1st of november till the 5th of november) as hyperlinks in sheets("Found") so I can click on them and open them. Just open them one at a time when they are put as hyperlinks in the sheet. Like looking in Explorer but now you only see the desired date range and not all the files.

I know we can open Explorer, go to the network drive, select the desired files and open them but it's nicer to stay in the workbook were we also have the search option. So with this workbook we can search for words of a desired month but now we also just want to find some reports of a desired date range and open them (not to look for words in the text but just to read the report).

Thanks and regards.

[Ken Puls]

Hi Airbrone,

Truth be told, it probably would have been easier for me if I wasn't part of the last one! I have to get my mind out of what we did last time and into a new frame of mind for this task!

Okay, so here's what I'm thinking:
-You have your userform and put in the 'date from' and 'date to'
-Those dates are passed to a routine
-The routine picks the file folder and creates a hyperlinked list of all files that have any date in that range in the title (one line per file)

That sound about right so far? If I remember correctly, your file structure looks like this though:

\\Disk1\Data\Year\mmm\ with mmm being the month, and each month's files being stored in those directories. Is that correct?

If so, we're going to have to build it smart enough to search through a couple of different folder should the user enter a range that spans over a month end. (That'll be the tricky part, but it can be done.)

Cheers,

[Airborne]

Hi Ken, glad you are positive about it because one VBA pupil can ask more then 10 VBA specialists can answer I guess . I'm going through the training on the forum now and it's really worthwhile. But I'm just starting on lesson 4, so much to do.

You are right about the path.
I would like to keep the Form you designed. Have one textbox where you type for instance Nov-2004 and then a textbox where you type 10-15 (the reports from the 10th till the 15th of november). But I would also like to have the option to just choose the 10th of november (so a range or just 1 day).

Thanks so far.

[Ken Puls]

I would like to keep the Form you designed. Have one textbox where you type for instance Nov-2004 and then a textbox where you type 10-15 (the reports from the 10th till the 15th of november). But I would also like to have the option to just choose the 10th of november (so a range or just 1 day).

Okay... well... I tweaked it a bit.

So here's the deal with what I did. I renamed the form to something slightly different so that you can import it into your project without blowing away the other one. (we can't use exactly the same form, as it was designed for different methods. I'm thinking that it's mainly the progress bar that you want to keep though... I've worked with that.

FYI, all the code in this one is self contained, so we don't have to worry about it conflicting with the other modules.

You'll notice that it asks for a start and end date, instead of a month/year and then day range. The reason for this is that it makes it easier to loop through each day in the range. It then runs a loop inside each day, to see if the files are there.

This is smart enough to deal with ranges spanning over months, or even years.

The only part that concerns me here is that it adds a hyperlink for each file it finds... so if there happen to be 6 files for one day you get six hyperlinks. We can work on that.

Import this into your project and take it for a test drive. Use dates that do exist, dates that you don't have data for, and even a date that it a couple of months from now.

Let me know if this hits the mark, or is way off base from what you were after.

Cheers,

[Airborne]

Hi Ken, thanks. It works but there are a few problems:

If I search for reports 9-10-2004, the search bar shows searching for files 09-10-2004. They don't excist however but then it shows hyperlinks 19-10-2004 and 29-10-2004 and 9-10-2004. Our reports are in the format 7-10-2004, 8-10-2004, etc.

When the hyperlinks are loaded on the "Found" sheet it messes up my sheet. The cell ranges are changed. But I want to use the same setting as when I search for text in files. I want to put the files in B6:E24.

When the files are shown on the "Found" sheet I only want to see the file name and not the total path.

I feel a little telling you all this, more work!!

Thanks anyway and regards.

[Ken Puls]

Hi Airborne!

Hmmm... yes I can see that being an issue. I didn't even think about that! I always name my files mm-dd-yyyy (05-05-2004) so that they sort correctly in lists, as I hate it when my list goes 1,11,12...19,2,20...etc. Regardless, we can make that go, but we made need to approach it differently.

I don't know if we'll be able to use the Instr function (currently in use) for the reason you mentioned above, so we may need to count characters and attack it that way. Are you familiar with the Left, Right and Len functions? (If not, you may want to review those in the standard Excel help, as they'll come in to play here.)

As for messing up your sheet... well... yeah! I actually coded it to completely clear out all data and formatting on the sheet! We can change that though. So should we be writing the values to B6, C6, D6, E6 then B7, C7...? Or down then up? It's easier to put them all in one column, but again, we can make this work.

As for the total path, is that what it's showing? You're using XL2003, right? I thought I coded it to only put the filename as the mask. OUt of curiosity, assume that we've pulled file Data5-11-2004_1_morning.xls, what do you want it to say on the hyperlink?

Cheers,

[Airborne]

Hi Ken,

as I hate it when my list goes 1,11,12...19,2,20...etc.

. I guess you are right about that. I hate that too. The problem is that I have to rename many, many files to change that. But does that mean if I want to get the files 1-11-2004 till 20-11-2004 that the hyperlinks on the "Found" sheet will show up in that irritating manner?

So should we be writing the values to B6, C6, D6, E6 then B7, C7...? Or down then up?

Down then up please .

As for the total path, is that what it's showing? You're using XL2003, right? I thought I coded it to only put the filename as the mask

I'm very sorry , the names of the files are showing like I wanted and like you planned. Forget my remark.

Thanks and regards.

[Ken Puls]

Hey there,

Don't rename your files. Let's see if we can make it work the way you want first. I think that doing that much work would be a last resort. I only mention it in case you're setting up a new system in the future as it may be something you want to consider then.

I'm pretty sure that we can make them show up in order. I'll have to think a bit about filling the range. Filling the range of B6:E24 means you have a limit of 16 files returned, as well. I assume that we just want to quit entering data (exit the loop) after that point?

[Airborne]

Hey Ken, sorry I mean Range("B6:F24"), 95 files. The maximum files for one month could be 93. So it should be alright.


I'm glad I don't have to rename the files for now .

Regards.

[Ken Puls]

Hi there,

Sorry for the delay in getting back to you on this one. I wanted to be able to sit down and think this one through... mostly to do with the data placement. So let's try this one out... A summary of the changes:

-Set to clear Range("B6:F24") at beginning of procedure
-Had to add a row/column counter set in the routine to allow data to be placed in the range down the column first, then across to next column (uses the cells(r,c) object, rather than range("A65536").end(xlup)
-Also added a bailout in case more than 95 matches were found (19x4 cells) so the procedure just exits at this point
-Dropped InStr in favour of some left/right text functions to ensure that file names can deal with single digit days and/or months in the date strings

Option Explicit
Private Sub cmdOkay_Click()
    Const MainPath = "\\Disk1\Data\Year"
    Dim Prompt As String, FullFilePath As String, _
        StartDate As Date, EndDate As Date, x As Date, _
        fso As Object, fs As Object, _
        FilesToProcess As Integer, i As Integer, Countfiles As Integer, _
        wsTarget As Worksheet, _
        r As Integer, c As Integer, _
        datelength As Long
'Turn off screen updating
    Application.ScreenUpdating = False
    Set wsTarget = Worksheets("Found")
'Make sure both textboxes have date values assigned
    If Not IsDate(tbFromDate.Value) Then
        Prompt = "Please enter a valid date in the From field" & vbCrLf
    Else
        StartDate = tbFromDate.Value
    End If
If Not IsDate(tbToDate.Value) Then
        Prompt = Prompt & "Please enter a valid date in the To field"
    Else
        EndDate = tbToDate.Value
    End If
'If Prompt is empty, then no problems were detected
    If Prompt = "" Then
        'Clear out the worksheet
        With wsTarget
            .Range("B6:F24").Clear
        End With
'Initialize C to place data in the first column of the data storage field
        '(This is used to start in column B below - cells(r+5,c+1)
        c = 1
'Create a file scripting object and set the FullFilePath variable
        Set fso = CreateObject("Scripting.FileSystemObject")
'Create a file search object to work with the files inside the loop
        Set fs = Application.FileSearch
'Expand userform to show progress bar
        Me.Height = 174
'For each date in the range entered, search for matching files
        For x = StartDate To EndDate
'Create the path to the files based on the date being examined
            FullFilePath = MainPath & Year(x) & "" & Format(x, "mmm") & ""
            datelength = Len(Format(x, "d-m-yyyy"))
'Update the progress indicator title, and set progress back to start
            With Me
                .lblProgDesc.Caption = "Searching for files containing " & Format(x, "dd-mm-yyyy")
                .frmProgress.Caption = "0% complete"
                .lblProgress.Width = 0
                .Repaint
            End With
'Check if the file path exists (using file scripting object)
            If fso.FolderExists(FullFilePath) Then
                Countfiles = 0
'Search for matching files (using file search object)
                With fs
                    'Set the directory to look in
                    .LookIn = FullFilePath
                    'Search only Excel workbooks in the directory
                    .FileType = msoFileTypeExcelWorkbooks
                    'Execute the search
                    .Execute
'Count total files to process for progress indicator
                    FilesToProcess = .FoundFiles.Count
'Search through all files for filenames containing the date being
                    'evaluated, and create a hyperlink to those files
                    '(Displays only data after the word "Data" in the file name)
                    For i = 1 To .FoundFiles.Count
                        'Evaluate if the file name found matches that searched for
                        '(InStr returned 11-1-2004 as well as 1-1-2004 so chose left/right combo)
                        If Left(Right(.FoundFiles(i), _
                            Len(.FoundFiles(i)) - Len(FullFilePath) - 4), _
                            datelength) = Format(x, "d-m-yyyy") Then
'Increment the Row and Column counters to place the data
                            If r < 19 Then
                                r = r + 1
                            Else
                                r = 1
                                c = c + 1
                            End If
'If column > 5 then too many matches have been found to fit in the data
                            'range, so exit procedure
                            If c > 5 Then GoTo ExitPoint
'Add the hyperlink in the correct row/column
                            wsTarget.Hyperlinks.Add _
                                Anchor:=wsTarget.Cells(5 + r, 1 + c), _
                                Address:=.FoundFiles.Item(i), _
                                TextToDisplay:=Right(.FoundFiles(i), _
                                    Len(.FoundFiles(i)) - Len(FullFilePath) - 4)
End If
                        'Count files processed for progress indicator
                        Countfiles = Countfiles + 1
'Update the progress indicator
                        Me.frmProgress.Caption = Int(Countfiles / FilesToProcess * 100) & "% complete"
                        Me.lblProgress.Width = Countfiles / FilesToProcess * (Me.frmProgress.Width - 10)
                        Me.Repaint
                    Next i
                End With
            Else
            'If directory does not exist, mark this in the list
                wsTarget.Range("A65536").End(xlUp).Offset(1, 0).Value _
                    = FullFilePath & " does not exist.  No data found for " & Format(x, "d-mm-yyyy")
            End If
        Next x
    Else
    'If Prompt is not empty, tell the user what info need correcting and return to the
    'userform
        MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
        vbCritical + vbOKOnly, "Please try again!"
    End If
ExitPoint:
    'Turn screen updating back on, release all objects, and unload the userform
    Application.ScreenUpdating = True
    Unload Me
End Sub

I tried to document the code fairly well so you can follow what it's doing all the way through. It can be a bit confusing though, as it uses loops within loops... particularly when some variables are set within the first, and not the second vs when they're set in the innermost...

If you have any questions on any of it, just let me know, and I'll see if I can explain better. And, of course, if something doesn't work, or you want to modify it a bit, let me know!

Cheers,

[Airborne]

Hi Ken. I don't know what the problem is but when I start the search the progress bar appears. Then after some time you see the color of the bar filling up very fast and then the form unloads and then nothing. When I look at the "Found" sheet I can see that something happened because the cells sizes are changed but the files are not there.

When I try 12-Dec-2006 I will get the text on the "Found" sheet because of [VBA]'If directory does not exist, mark this in the list
wsTarget.Range("A65536").End(xlUp).Offset(1, 0).Value _
= FullFilePath & " does not exist. No data found for " & Format(x, "d-mm-yyyy")
[/VBA]
So that works. But if I try 12-Dec-2004 it will start searching and after a while the form will unload without any message.

I'm sorry, I guess I'm starting to create this situation


thanks and regards.

[Ken Puls]

Hmmm.. puzzling...

I'm assuming that the Year2006\Dec directory obviously doesn't exist. Out of curiosity, do you have a directory created already for Year2004\Dec, and if so, does it have any relevant files in it?

If I'm right ( ) and you do have the folder created, but no relevant files... well, then it's working exactly as designed. We could add something in the loop to add a line if no data was found for that day, which would make it a little more obvious to the user.

If not... well... I'll need some more details on what folders you do have set up (like the December one) and what file names you do have in there. I set up a mock directory here, and created a bunch of files with names per you convention to test, so I should be able to work it out.

Let me know,

[Airborne]

You are right Ken, there are no files in de Dec map. So that's working as you designed and thought it should work.

But searching on a map filled with files (e.g. Nov) gives the result as I mentioned..

I don't know what the problem is but when I start the search the progress bar appears. Then after some time you see the color of the bar filling up very fast and then the form unloads and then nothing. When I look at the "Found" sheet I can see that something happened because the cells sizes are changed but the files are not there.

[Ken Puls]

Hi Airborne,

I'm not sure if this will make a difference, but maybe try doing a find/replace, replacing "d-m-yyyy" with "d-mm-yyyy"

If that doesn't work, can you post exactly what date range you tried, exactly what the path to the directory is, and a list of files you expected to find?

Thanks,

[Airborne]

Hello Ken, I've tried

replacing "d-m-yyyy" with "d-mm-yyyy"

but no files on the "Found" sheet.
I fill in the date on your form textboxes, e.g. 9-Nov-2004 and 10-Nov-2004.

Your first form in this thread worked. It found the files and placed them in the sheet. Problem was, it couldn't see the difference between 9-11-2004 and 19-11-2004.

So the path is correct "\\Disk1\Data\Year". The files are stored in the month. So the total path is for instance \\Disk1\Data\2004\Nov and the filename Data9-11-2004_1_Night.

Thanks so far and regards.

[Ken Puls]

Ahh!

Okay, I think I know what the problem is then... why it worked the first time though, I'm not sure...

Check out this line:

Const MainPath = "\\Disk1\Data\Year"

Later in the sub, we add the year to this string with this:

FullFilePath = MainPath & Year(x) & "\" & Format(x, "mmm") & "\"

So, for the date of 9-Nov-2004, we would end up with a path of:
\\Disk1\Data\Year2004\Nov\

That's not right, though... although I'm not sure why you'd be getting no data, and not the error about no folder exists...

Try changing that Const line to =\\disk1\data\ and see if that makes a difference.

Let me know,