vba to activate workbook with a certain length to the file name

[BORUCH]

hi

i have sometimes multiple workbooks open at the same time i would like to have a vba code that activates the window that the file name looks like this

AA123-000000

So it would need to find a file name that is formatted with two letters, with 3 numbers after that, and a dash and six digits after that

thanks

 

[maabadi]

Try this:

Sub SelectWorkbook()
Dim i As Long, L As Long, P As String, j As Long, K As Long
For i = 1 To Workbooks.Count
L = Len(Workbooks(i).Name)
For j = 1 To L
P = Mid(Workbooks(i).Name, j, 1)
Select Case j
Case 6
If P = "-" Then K = K + 1
Case Is < 3
If P Like "[A-Za-z]" Then K = K + 1
Case Is >= 3
If P Like "#" Then K = K + 1
End Select
Next j
Debug.Print K
If K = 12 Then
Workbooks(i).Activate
Debug.Print ActiveWorkbook.Name
End If
K = 0
Next i
End Sub

 

[HaHoBe]

Hi,

a different approach

Sub MrE1171060()
'https://www.mrexcel.com/board/threads/vba-to-activate-workbook-with-a-certain-length-to-the-file-name.1171060/
  Dim wb            As Workbook
  Dim varSplit      As Variant
  Dim varWbName     As Variant
 
  For Each wb In Workbooks
    varSplit = Split(wb.Name, ".")
    If Len(varSplit(0)) = 12 Then
      varWbName = Split(varSplit(0), "-")
      If IsNumeric(varWbName(1)) And Len(varWbName(1)) = 6 Then
        If IsNumeric(Mid(varWbName(0), 3, 3)) Then
          If Left(varWbName(0), 1) Like "[A-Za-z]" And Mid(varWbName(0), 2, 1) Like "[A-Za-z]" Then
            wb.Activate
            Exit Sub
          End If
        End If
      End If
    End If
  Next wb

End Sub

Ciao,
Holger

 

[BORUCH]

Try this:

Sub SelectWorkbook()
Dim i As Long, L As Long, P As String, j As Long, K As Long
For i = 1 To Workbooks.Count
L = Len(Workbooks(i).Name)
For j = 1 To L
P = Mid(Workbooks(i).Name, j, 1)
Select Case j
Case 6
If P = "-" Then K = K + 1
Case Is < 3
If P Like "[A-Za-z]" Then K = K + 1
Case Is >= 3
If P Like "#" Then K = K + 1
End Select
Next j
Debug.Print K
If K = 12 Then
Workbooks(i).Activate
Debug.Print ActiveWorkbook.Name
End If
K = 0
Next i
End Sub

Thank you very much

 

[HaHoBe]

Hi BORUCH,

maybe give maabadi´s code a go with a collection of workbooks named AA123-000000, AA123-000000a and AA123-000000abcd open. Which of these files should be activated?

Ciao,
Holger

 

[Peter_SSs]

[.. or try HaHoBe's code with AA123-000000.Tuesday.xlsx or AA123-1234E1.xlsm]

My suggestion

Sub Activate_WB()
  Dim wb As Workbook

  For Each wb In Workbooks
    If Left(UCase(wb.Name), InStrRev(wb.Name, ".")) Like "[A-Z][A-Z]###-######." Then
      wb.Activate
      Exit For
    End If
  Next wb
End Sub

 

[BORUCH]

Thank you very much

Try this:

Sub SelectWorkbook()
Dim i As Long, L As Long, P As String, j As Long, K As Long
For i = 1 To Workbooks.Count
L = Len(Workbooks(i).Name)
For j = 1 To L
P = Mid(Workbooks(i).Name, j, 1)
Select Case j
Case 6
If P = "-" Then K = K + 1
Case Is < 3
If P Like "[A-Za-z]" Then K = K + 1
Case Is >= 3
If P Like "#" Then K = K + 1
End Select
Next j
Debug.Print K
If K = 12 Then
Workbooks(i).Activate
Debug.Print ActiveWorkbook.Name
End If
K = 0
Next i
End Sub

Good morning

i would like if posibile to add one more thing to the code i have a scenario where the file name can be like AA123-X00000 OR BB123-X33333

Can you please add this scenario to the above code

thanks

 

[maabadi]

With @Peter_SSs Method, Use this:

Sub Activate_WB()
  Dim wb As Workbook
  For Each wb In Workbooks
    If Left(UCase(wb.Name), InStrRev(wb.Name, ".")) Like "[A-Z][A-Z]###-?#####." Then
      wb.Activate
      Exit For
    End If
  Next wb
End Sub

 

[BORUCH]

With @Peter_SSs Method, Use this:

Sub Activate_WB()
  Dim wb As Workbook
  For Each wb In Workbooks
    If Left(UCase(wb.Name), InStrRev(wb.Name, ".")) Like "[A-Z][A-Z]###-?#####." Then
      wb.Activate
      Exit For
    End If
  Next wb
End Sub

Thank you very much