Way to exclude certain files from my list?

[jazminecat]

Hi all - I have the following great code which populates a text box on my form with the contents of a folder on my network:

'last updated 1/4/2006


Public Sub Form_Current()
   Dim ScrObj
   Dim SearchFld
   Dim mFileName
   Dim strRowSource As String
   Dim MyBid As String
   Dim BidName As String
   Dim nFolderName
   
   
   myyear = Right(Me.Year, 2)  'get the right two digits of the year
   
   '------get the bid number-----------
   'add a zero if bidnumber is a single digit
   If Me.BidNum < 10 Then
   MyBid = "0" & Me.BidNum
   Else
   MyBid = Me.BidNum
   End If
   
   BidName = myyear & "-" & MyBid 'create folder name from year and bidname
   
   '---create base folder name to open folder for year
   
    BaseFolderName = "J:\Bulletin Board\Departments\As\Finance\Purchasing Bid & RFP" & "\" & myyear & " Bids"
    Set ScrObj = CreateObject("Scripting.FileSystemObject")
    Set SearchFld = ScrObj.GetFolder(BaseFolderName)
    
   '---look at each folder and create folder path based on bid name xx-yy
   
   For Each nFolderName In SearchFld.SubFolders
        If Mid(nFolderName, Len(SearchFld) + 2, 5) = Left(BidName, 5) Then
          FolderName = strRowSource & nFolderName.Name
          Exit For
          Else: FolderName = ""
        End If
   Next
   '---error trapping, if folder is named incorrectly, is empty, or doesn't exist
    If FolderName = "" Then
          'MsgBox "There is no folder for this RFP", vbOKOnly
          Me.fldContents.RowSource = "No Folder"
          Exit Sub
    End If
      
   Set SearchFld = ScrObj.GetFolder(BaseFolderName & "\" & FolderName)
   For Each mFileName In SearchFld.Files
    strRowSource = strRowSource & mFileName.Name & ";"
    Next
    Me.fldContents.RowSource = strRowSource
    Debug.Print BaseFolderName & "\" & FolderName
    
End Sub

I got this working a few months back with no small amount of help from y'all on this forum.

I'm wondering now if there is a way to exclude certain files witnin a folder from appearing in my text box - specifically, files that began with a specific character string - "QQ" for example? We're trying to find a way for draft documents to be stored on the network without appearing in this database, and without having to store them elsewhere - why have two folders for storing related documents, is the theory. oO I was thinking, why not save any draft docs with a certain character string at the beginning, and then delete that character string on the finalized document. Even my remedial end-user could manage that, I believe.

As always, thank you in advance for your time.

 

[TomCologne]

The following code should at least be a start.

[COLOR=green] '' ....
'' Execute only when file name does not start with "QQ":
[/color green] 
If Left(mFileName.Name,2) <> "QQ" Then

    strRowSource = strRowSource & mFileName.Name & ";"
    Next
    Me.fldContents.RowSource = strRowSource
    Debug.Print BaseFolderName & "\" & FolderName

End if

You might want to look deeper into these VBA functions:

Left(), Mid(), Right(), InStr(), InStrRev()


TomCologne