Set workbook which is already open with specific name

[Anantha Krishna]

Hello All,

Please help me for the below requirements

I need to set workbook which is already open with specific name, E.g: If file name is "xyz Filename abc.xlsx", i need to set this workbook with the name Filename, because every time file name will change which is before and after "Filename".

I am using this below code, but it is not working

Dim sWb As Workbook
    Dim sWs As Worksheet

Set sWb = Workbooks("*Filename*.xlsx")
    Set sWs = aWb.Sheets("sheet1")

Thanks in Advance
Anantha

 

[Marc L]

Hi !​

• You can't use any wildcard for a workbook name but just the complete name !
  
  
• If you create an object variable - often useless ! - via Set statement
  so do not forget to release it before the procedure ends like Set ObjVariable = Nothing …
  
  
• If you open the "filename" workbook via the VBA code so once opened it is the active workbook
  so you can easily create the worksheet variable - again if really needed - like Set Ws = ActiveWorkbook.Sheets("Sheet1") …
  
  
• Instead of a worksheet variable you can use the With statement …

 

[shrivallabha]

You can use below approach to loop through all workbooks...

Public Sub CheckForWorkbook()
Dim wbReqd As Workbook, wb As Workbook
For Each wb In Application.Workbooks
    If InStr(1, wb.Name, "FileNameComesHere", vbTextCompare) > 0 Then
        Set wbReqd = wb
    End If
Next wb

If wbReqd Is Nothing Then
    MsgBox "Required workbook is not open!", vbInformation
Else
    '\\ further processing...
End If

End Sub

 

[Anantha Krishna]

Hi !​

• You can't use any wildcard for a workbook name but just the complete name !
  
  
• If you create an object variable - often useless ! - via Set statement
  so do not forget to release it before the procedure ends like Set ObjVariable = Nothing …
  
  
• If you open the "filename" workbook via the VBA code so once opened it is the active workbook
  so you can easily create the worksheet variable - again if really needed - like Set Ws = ActiveWorkbook.Sheets("Sheet1") …
  
  
• Instead of a worksheet variable you can use the With statement …

Thanks for your reply Marc

 

[Anantha Krishna]

You can use below approach to loop through all workbooks...

Public Sub CheckForWorkbook()
Dim wbReqd As Workbook, wb As Workbook
For Each wb In Application.Workbooks
    If InStr(1, wb.Name, "FileNameComesHere", vbTextCompare) > 0 Then
        Set wbReqd = wb
    End If
Next wb

If wbReqd Is Nothing Then
    MsgBox "Required workbook is not open!", vbInformation
Else
    '\\ further processing...
End If

End Sub

Thanks for the solution Shrivallabha, it is working.