search folder for file

**Jay59** · 12-17-2008, 09:59 PM

I'm using Excel 2007, so no .filesearch

I want to search a folder for a file containing a certain phrase. Would like to use a case option like in java.

search for file containing "aaa", "bbb", "ccc", or "ddd"

then if file exists

Select Case True
    Case file contains "aaa"
        Run macro aaa
    Case file contains "bbb"
        Run macro bbb
    ...
End Select

-I'm not sure the correct syntax for the above.

This is gonna be a long process to get the code where I want it, but I taking one step at a time, so I understand what is happening and I can change if I need.

TIA

Wanted to add that the folder can be set to only have 1 file, but would like to be able to have multiple files while testing.

**rylo** · 12-17-2008, 10:23 PM

Hi

Does DIR() work in 2007?

What sort of files are you testing? Excel?

rylo

**Jay59** · 12-17-2008, 11:03 PM

Yes and they are excel files. Once i get this down, each file has different set of instructions.

Basically all files are opened and edited with vba code, saved, then imported to access database. After that happens, I open another file that is linked to access database to generate reports, then i copy and past pivot tables to email and send reports that way.

Right now its 5 a day and another 10 weekly.

**rylo** · 12-17-2008, 11:39 PM

Hi

Ok so you should be able to use DIR to cycle through the files, open any excel file, cycle through the sheets in the file and do your search(s).

If all the data is being copied into an access database, then why don't you just extract it from there into excel using SQL. Don't have to work your way through the files. Or do you have to different processing on the files to make them in a shape to put into Access?

If you can describe in more detail what you are doing, there may be alternative approaches that would simplify things...

rylo

**Jay59** · 12-18-2008, 12:57 AM

Total of 5 files a day, 3 files have to edited before importing them into access.

Here is the complete process I hope to implement.

-Everyday from 7 to 12 in the morning i receive 5 files at least 15 mins apart.
When the file hits my inbox I have thunderbird(email client) to save it and open with default program.

So a change of plans i guess, i need this code to run on any file that is opened.(sorry just test this to see how it actually works.)

-Use a case statement to check the file name and execute code based on what the file name may contain. Its gonna be specific for example Report32145 would have to be in the file name for case 1.( this name will change everyday, but will always contain Report32145.(so i guess search for *Report32145*)

-After the case statement is used the specified macro is execute on the file, editing it to the requirement format.

-Save file to specified spot and with specified name.(all this is specified in the vba code to run based on case statement.

-Close file

-Then i would use a command after the close statement to open access database and run a macro that imports the file that was just saved.

-The database would close itself and open another file that refreshes its report.

-That file use vba to copy and paste date into email and send email.

I know this will be long process and take some time to setup, but it will be worth it.

**rylo** · 12-18-2008, 01:42 AM

Hi

Start with the access part. Create the file import procedure, then create an autoexec macro that will call this sub so that when the database opens, it will automatically import the data.

See how that goes.

rylo

**Jay59** · 12-19-2008, 02:38 PM

I'm working on doing this using the plugin for thunderbird, but in the mean time, i'm going to move forward.

First this will be how to search folder for files containing keywords.

Since I'm using 2007 i can't use filesearch, so how would I do this.

Thanks

**Kenneth Hobson** · 12-19-2008, 03:21 PM

When you say search for key words, do you mean in the filename or in the file's contents?

If the latter, try the API or ADO methods posted at: http://www.experts-exchange.com/Micr..._23483221.html If you try one of those, let us know what you used and how it worked out for you.

If the former, there are several ways to do it using the File System Object method or DOS. Here are 2: http://www.vbaexpress.com/forum/showthread.php?t=22245

**rylo** · 12-19-2008, 07:49 PM

Hi

Have you tried dir() in 2007? If it doesn't work, then maybe you will have to post in the 2007 forum to see if someone there knows how to do the file search.

rylo

**Jay59** · 12-31-2008, 12:39 PM

I accomplished by filling an array with the file names and searching the names in the array. Since my folder will only have the files that I need to work with the file count will be below 10, so this method works fast.

Public Sub ListWorkbooks()
   Dim Directory As String
   Dim FileName As String
   Dim IndexSheet As Worksheet
   Dim Count As Long
   Dim MyNames() As String
   Dim Max As Integer
   Dim FileReName As String
   Dim temp As String
   Dim i As Integer
   
   'Disable Alerts
   Application.DisplayAlerts = False
      
   'ReportPath is a constant
   Directory = ReportPath
   If Left(Directory, 1) <> "\" Then
       Directory = Directory & "\"
   End If
   
   'Get count of files and redefine array with file count
   Max = FileCountA(Directory)

   If Max = 0 Then Exit Sub

   ReDim MyNames(1 To Max)
   Count = 1

   'Worksheet used for testing
   'Set IndexSheet = ThisWorkbook.ActiveSheet
    
   'Fill Array with file names
   FileName = Dir(Directory & "*.xls")
   Do While FileName <> ""
       'IndexSheet.Cells(Count, 1).Value = FileName
       MyNames(Count) = FileName
       Count = Count + 1
       FileName = Dir
   Loop
   
   Count = 1
   
   'Process files and output to workbook for testing
   Do While Count <= Max
        'IndexSheet.Cells(Count, 1) = MyNames(Count)
        'IndexSheet.Cells(Count, 2) = CharterReportFileRename(MyNames(Count))
        'Fuction used to process file based on file name
        Call MoveAndProcess(Directory, MyNames(Count), CharterReportFileRename(MyNames(Count)))
        Count = Count + 1
        FileReName = ""
        
        Application.Wait Now + TimeValue("00:00:05")     'used to reduce errors early on, prob not needed now
   
        For Each wb In Workbooks
            If wb.Name <> "PERSONAL.XLSB" Then wb.Close False
        Next wb
   Loop
    
   Set IndexSheet = Nothing
   
   'Enable Alerts
   Application.DisplayAlerts = True

**Kenneth Hobson** · 12-31-2008, 12:53 PM

Not sure what FileCountA handles. I would suggest that you add code to handle the case where Max=0.

The dictionary object can be handy for such things. e.g.

Sub GetMyFiles()
  Dim myFolder As String, wcFiles As String, s() As Variant
  'Input parameters
  myFolder = "x:\Excel" 'No trailing backslash
  wcFiles = "*.xls"
  s = MyFiles(myFolder, wcFiles)
  'Check to see if s() has any filenames
  If s(0) = "NA" Then Exit Sub
  
  'Put the contents of s() into column A
  Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(s) + 1).Value _
    = WorksheetFunction.Transpose(s)
  
End Sub

Function MyFiles(myFolder As String, wcFiles As String) As Variant
'Requires reference to Microsoft Scripting Runtime
  Dim cFiles As New Scripting.Dictionary
  Dim FileName As String, a() As Variant
  
  'Put filenames into dictionary
  FileName = myFolder & "\" & Dir(wcFiles)
  Do While FileName <> myFolder & "\"
    cFiles.Add FileName, Nothing
    FileName = myFolder & "\" & Dir
  Loop
  
  'Return keys or items as an array
  If cFiles.Count > 0 Then
    a = cFiles.Keys
    MyFiles = a
    Else
    ReDim a(1) As Variant
    a(0) = "NA"
    MyFiles = a
  End If
  Set cFiles = Nothing
End Function

**Jay59** · 12-31-2008, 01:02 PM

Thanks

FileCount returns the number of files in directory.

Thanks for the suggestion, as of right now I have very little error handling in my code, I'm working on adding it.

So I'll add

If Max = 0 Then Exit Sub

search folder for file

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

search folder for file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions