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?
'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