VBA Excel Set Workbook with name like something

February 20, 2024

I have a situation, in which my code needs to be referred to the external workbook, which has been created just recently. Therefore it doesn’t have a generic name. Because the name is default it always comes as Book1, Book2, Book3, etc.

I found a nice hint here:

VBA recognizing workbook by partial name

And applied to my situation like below:

 Dim mwkt As Workbook, pwkt As Workbook
 Dim wbName As String
 wbName = "Book"
 For Each pwkt In Application.Workbooks
 If pwkt.Name Like wbName & "*" Then
 Set pwkt = pwkt.Name
 End If
 Next pwkt

  Set pwkt = pwkt.Name  retiurns the **'Type mismatch'** error.

Is there any way of sirting this out?

>Solution :

‘pwkt.Name’ will return a String but you have declared pwkt as a Workbook. That is the type mismatch.

You are also using the variable pwkt for the loop and also for the setting within the loop. Perhaps you mean something like this?

Dim mwkt As Workbook, pwkt As Workbook, wb as Workbook
Dim wbName As String
wbName = "Book"
For Each wb In Application.Workbooks
    If wb.Name Like wbName & "*" Then
        Set pwkt = wb
    End If
Next wb