Checking if another workbook is open

[Matthew Balch]

Hi,

I have a mcro that extracts data from another workbook which is created from
a download of another application.

How do I in VB write it to check to see if this download is available. The
workbook I need to check to see if it is open in the application is called
'Worksheet in basis'.
If not available then will simply cancel the macro and prompt the user to
extract the data accordingly.

Thanks in advance
Matthew Balch

 

[Bob Phillips]

On Error Resume Next
Set oWB = Workbooks(Filename:="Worksheet in basis.xls")
On Error Goto 0
If oWB Is Nothing Then
MsgBox "Workbook not open"
Exit Sub
End If

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Don Guillett]

Sub GetWorkbook()' from a printed name in a cell
If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows(workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
End Sub

Sub GetWorkbookA()
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("yourfilename.xls")
If wBook Is Nothing Then
Workbooks.Open ' <File and path>
Else
wBook.Activate
End If
On Error GoTo 0
End Sub

 

[Chip Pearson]

Matthew,

You can use a function like

Public Function IsWorkbookOpen(WorkbookName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = _
CBool(Len(Application.Workbooks(WorkbookName).Name))
End Function

Then, you'd call the function in code like the following:

Sub AAA()
Dim Res As Boolean
Res = IsWorkbookOpen("Worksheet in basis.xls")
If Res = True Then
'''''''''''''''''''''''''''
' code for workbook is open
'''''''''''''''''''''''''''
Else
'''''''''''''''''''''''''''''''
' code for workbook is not open
'''''''''''''''''''''''''''''''
End If
End Sub

Note that the workbook name must include the ".xls" extension in the name.
E.g.,

"Worksheet in basis.xls"
and NOT
"Worksheet in basis"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[bigpalooka]

I always use this method - it's simple and works well.
jp

 

[Vijay Kotian]

Hi
I tried to use the code given by you. I am getting error message run time
error '9' subscript out of range.. can you please help me out.

Regards
Vijay Kotian

 

[Dave Peterson]

You changed part of the code.

Either copy Bob's response and try again or post your current code.