VBA - Testing for Book1, Book2 etc.

[dhstein]

Every day Quickbooks will create an excel file and depending on what is going
on on the user's system, it may be created as Book1, Book2 etc. In another
file on the user's machine, I'd like to create a macro that will save that
file to the network with a specific name. So I need a way of testing if
there is an open workbook called Book1, Book2 etc. There will only be one
Bookx file so if I find anything Bookx that's the one I need. Thanks for any
help on this.

 

[Jacob Skaria]

Paste the below function in your code and use it as

If Checkforworkbook ("Book25.xls") = True Then
'do something
End If

Function CheckforWorkbook(strSearch As String) As Boolean
Dim intTemp
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
CheckforWorkbook = True: Exit Function
End If
Next
End Function

 

[Jacob Skaria]

Paste the below function in your code and use it as

If Checkforworkbook ("Book25.xls") = True Then
'do something
End If

Function CheckforWorkbook(strSearch As String) As Boolean
Dim intTemp
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
CheckforWorkbook = True: Exit Function
End If
Next
End Function