?
Solved

Excel VBA search for a string in an array

Posted on 2011-10-31
8
Medium Priority
?
1,115 Views
Last Modified: 2012-05-12
I have a valid array in VBA. I am wanting to search for a string in the values that are in the array and return the location that the value is located at. In my specific code I have a list of file names. For the sake of this question I have the following

arReportsFileList(1) = "The First File Name.xls"
arReportsFileList(2) = "Test_Client_Mapping_File.xls"
arReportsFileList(3) = "Another file name.xls"

I have the following code from http://msdn.microsoft.com/en-us/library/aa164525(v=office.10).aspx

Function FilterExactMatch(astrItems() As String, _
                          strSearch As String) As String()
                  
   ' This function searches a string array for elements
   ' that exactly match the search string.

   Dim astrFilter()   As String
   Dim astrTemp()       As String
   Dim lngUpper         As Long
   Dim lngLower         As Long
   Dim lngIndex         As Long
   Dim lngCount         As Long
   
   ' Filter array for search string.
   astrFilter = Filter(astrItems, strSearch)
   
   ' Store upper and lower bounds of resulting array.
   lngUpper = UBound(astrFilter)
   lngLower = LBound(astrFilter)
   
   ' Resize temporary array to be same size.
   ReDim astrTemp(lngLower To lngUpper)
   
   ' Loop through each element in filtered array.
   For lngIndex = lngLower To lngUpper
      ' Check that element matches search string exactly.
      If astrFilter(lngIndex) = strSearch Then
         ' Store elements that match exactly in another array.
         astrTemp(lngCount) = strSearch
         lngCount = lngCount + 1
      End If
   Next lngIndex
   
   ' Resize array containing exact matches.
   ReDim Preserve astrTemp(lngLower To lngCount - 1)
   
   ' Return array containing exact matches.
   FilterExactMatch = astrTemp
End Function

Open in new window


And in my code I have the following to test this function out:
Function GetFileList(PathToReports As String) As Long
' Puts the files found in the directory in an array
' and Returns a total count of files found.
Dim strFileNames As String
Dim iCount As Integer
    iCount = 0
    ChDir PathToReports
    strFileNames = Dir(PathToReports & "\*.xls") 'Gets a list of the XLS files to process
     
     Do While strFileNames <> ""
        iCount = iCount + 1
        ReDim Preserve arReportsFileList(1 To iCount)
        arReportsFileList(iCount) = strFileNames
        strFileNames = Dir 'Get the next file in the directory
    Loop
    GetFileList = iCount
    
    ' Temporary code to test the finding of a string in an array
    MsgBox FilterExactMatch(arReportsFileList, "Client_Mapping")


End Function

Open in new window


The problem I am having seems to be dealing with the failure of my array of file names (arReportsFileList) to successfully pass to the function. The array in the function is empty.

I would appreciate any help in either how to make this code work, how to properly pass an array, or how to achieve the end result of finding a partial string in an array and returning the position of that string in the array.
0
Comment
Question by:ckelsoe
  • 4
  • 4
8 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 37058569
There are several problems with the posted code.
Undeclared variables
Failure to handle case of 0 matches returned by Filter function
Failure to handle case of 0 exact matches
Failure to reference a specific array element in the returned array from FilterExactMatch

In the particular case you posted, I suspect you will get zero exact matches because there is no file extension on the match string. If you do add the extension, then there will only be one match--because you are searching only a single folder, so the MsgBox needs to reference array element 0.

Function FilterExactMatch(astrItems() As String, _
                          strSearch As String) As Variant
                  
   ' This function searches a string array for elements
   ' that exactly match the search string.

   Dim astrFilter()   As String
   Dim astrTemp()       As String
   Dim lngUpper         As Long
   Dim lngLower         As Long
   Dim lngIndex         As Long
   Dim lngCount         As Long
   
   ' Filter array for search string.
   astrFilter = Filter(astrItems, strSearch)
   
   ' Store upper and lower bounds of resulting array.
   lngUpper = UBound(astrFilter)
   lngLower = LBound(astrFilter)
   
   ' Resize temporary array to be same size.
   If lngUpper > -1 Then
   
        ReDim astrTemp(lngLower To lngUpper)
        
        ' Loop through each element in filtered array.
        For lngIndex = lngLower To lngUpper
           ' Check that element matches search string exactly.
           If astrFilter(lngIndex) = strSearch Then
              ' Store elements that match exactly in another array.
              astrTemp(lngCount) = strSearch
              lngCount = lngCount + 1
           End If
        Next lngIndex
        
         ' Resize array containing exact matches.
         If lngCount > 0 Then
             ReDim Preserve astrTemp(lngLower To lngCount - 1)
         
             ' Return array containing exact matches.
             FilterExactMatch = astrTemp
         End If
    Else
        FilterExactMatch = "None found"
    End If
End Function

Function GetFileList(PathToReports As String) As Long
' Puts the files found in the directory in an array
' and Returns a total count of files found.
Dim strFileNames As String, arReportsFileList() As String
Dim v As Variant
Dim iCount As Integer
    iCount = 0
    ChDir PathToReports
    strFileNames = Dir(PathToReports & "\*.xls") 'Gets a list of the XLS files to process
     
     Do While strFileNames <> ""
        iCount = iCount + 1
        ReDim Preserve arReportsFileList(1 To iCount)
        arReportsFileList(iCount) = strFileNames
        strFileNames = Dir 'Get the next file in the directory
    Loop
    GetFileList = iCount
    
    ' Temporary code to test the finding of a string in an array
    v = FilterExactMatch(arReportsFileList, "Client_Mapping")
    If IsArray(v) Then MsgBox v(0)


End Function

Open in new window

0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 37058595
To return the position of a string within an array of strings, you might take a look at using the Match function.

Dim v As Variant
v = Application.Match("Client_Mapping",stringArray,0)
If Not IsError(v) Then
     MsgBox v
End If
0
 

Author Comment

by:ckelsoe
ID: 37058768
byundt, I get a runtime error 13 - type mismatch. I tried changing my array from a string to a variant with the same results.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 81

Expert Comment

by:byundt
ID: 37058903
Assuming that you are testing the code I posted in my first comment, I just retested with three different test cases: no match for substring, match for substring but not full string, and match for complete string. All three cases returned the number of .xls, xlsm, xlt, xlsx, etc. files in my test folder. The test case with the exact match also returned the complete file name.

How exactly were you testing?
0
 

Author Comment

by:ckelsoe
ID: 37059010
Here is a test routine I am using to test this...

Sub TestArraySearch()
    Dim TheFiles(1 To 5) As String
    Dim TheSearchString As String
    Dim v As Variant
    
    TheFiles(1) = "XXX_Report1_20111031.xls"
    TheFiles(2) = "XXX_Report2_20111031.xls"
    TheFiles(3) = "XXX_Report3_20111031.xls"
    TheFiles(4) = "XXX_Report4_20111031.xls"
    
    TheSearchString = "Report3"
    
    
    v = Application.Match("Report3", TheFiles, 0)
    If Not IsError(v) Then
         MsgBox v
    End If

End Sub

Open in new window

0
 

Author Comment

by:ckelsoe
ID: 37059030
I added "*" to the beginning and end of the search string - that seems to work.

 v = Application.Match("*Report3*", TheFiles, 0)
0
 
LVL 81

Expert Comment

by:byundt
ID: 37059692
Note that MATCH will return the location (one-based index number) of the first matching string in your search array. And as you have already found, it can accept the * wildcard character (0 or more characters of any type) and the ? wildcard (a single character of any type).
0
 

Author Comment

by:ckelsoe
ID: 37059758
Right. I can be very specific in the search string to insure that there will only be one file found. I get a series of XLS files periodically that have the same filename but with a prefix and a date suffix that is different. So in a given directory (user selectable by my code) there will be a bunch of files with the same prefix and suffix (which is variable depending on where the data is from).  I am using the logic you helped me with to write code that will work with each specific report no matter what the prefix and suffix will be.

Thanks very much for understanding the issue and your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question