find a pattern in list of filenames (simple sytax?}

[ElBombay]

Hi board,

I've opened a file with data I want to transfer to a template-file. When the import is successfully processed, I 'Save As' the template file using the name of the current source. The source file must of course first be closed. To get the name of the file which must first be closed, I've written the following Function. I need simple syntax help please on the line w/ "?????." Users could theoretically use alt-Tab to see the name and enter it to an input box but I try to minimize liveware interface

/*
Function zReadAllOpenXLSs() As String 'OpenWorkbooks As String)
'
' Find filename matching a YYMMDDXX pattern
' Added to read Source W/B for imported puzle
' 2/20/18 at St. John's Hospital
'
Dim x As Long
Dim cPattern As String
Dim OpenWorkbooks() As String

ReDim OpenWorkbooks(1 To Workbooks.Count)

'Store Names Here
For x = 1 To Workbooks.Count
OpenWorkbooks(x) = Workbooks(x).Name

Next x

'Close Workbooks Here
For x = 1 To Workbooks.Count
cPattern = Left(OpenWorkbooks(x), 8)
If cPattern Like "999999XX" Then ' ???????
zReadAllOpenXLSs = OpenWorkbooks(x)
Exit For
End If
Next x

End Function
*/

 

[ElBombay]

A little searching gave me this workable solution but I would still be interested in checking for characters in the 7th and 8th positions for future generic use. Thanks for the attention.

'----------------------------------------------------------------------------
Function zReadAllOpenXLSs() As String 'OpenWorkbooks As String)
'
' Find filename matching a YYMMDDXX pattern
' Current version returns 1st filename starting w/ 6 numerals.
' Added to read Source W/B for imported puzle
 2/20/18 at St. John's Hospital
'
Dim x                            As Long
Dim cPattern As String
Dim OpenWorkbooks()     As String

ReDim OpenWorkbooks(1 To Workbooks.Count)
    
    'Store Names Here
    For x = 1 To Workbooks.Count
        OpenWorkbooks(x) = Workbooks(x).Name

    Next x

    'Identify Workbook Here
    For x = 1 To Workbooks.Count
        cPattern = Left(OpenWorkbooks(x), 6)
        If cPattern Like "######" Then                  ' YYMMDD pattern
            zReadAllOpenXLSs = OpenWorkbooks(x)
            Exit For

        End If
    Next x

End Function

 

[ElBombay]

I initially posted this question to save myself some time. As long as I got around to piecing together & testing the fairly basic elements needed, I thought I should post the results for future reference. I certainly owe the board for all the time it's saved me over the years, as well as for a few thing I never would've developed on my own.

'----------------------------------------------------------------------------
Sub Test_ReadXLS()
'
' If null-string is returned, set a default and/or disply error msg.
' 3/11/18
'
Dim cValue As String

    cValue = zReadAllOpenXLSs("######", 6)

End Sub
'----------------------------------------------------------------------------
Function zReadAllOpenXLSs(cTemplate As String, iLen As Integer) As String
'
' Find filename matching a YYMMDDXX pattern
' Current version returns 1st filename starting w/ 6 numerals.
' Added to read Source W/B for imported puzle;
'   Parms set to run generically.
' 3/11/18
'
Dim x                   As Long
Dim cPattern            As String
Dim OpenWorkbooks()     As String

ReDim OpenWorkbooks(1 To Workbooks.Count)
    
    'Store all open filenames
    For x = 1 To Workbooks.Count
        OpenWorkbooks(x) = Workbooks(x).Name

    Next x

    'Find source-workbook
    For x = 1 To Workbooks.Count
        cPattern = Left(OpenWorkbooks(x), iLen)
        If cPattern Like cTemplate Then
            zReadAllOpenXLSs = OpenWorkbooks(x)
            Exit For

        End If
    Next x

End Function

 

[shg]

Another way:

Sub Test_ReadXLS()
  Dim wkb As Workbook
  
  Set wkb = GetWorkbook("######*")
  If Not wkb Is Nothing Then
    ' do something useful
  End If

End Sub

Function GetWorkbook(sPatt As String) As Workbook
  Dim wkb           As Workbook

  For Each wkb In Workbooks
    If wkb.Name Like sPatt Then
      Set GetWorkbook = wkb
      Exit Function
    End If
  Next wkb
End Function