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

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)

"Matthew Balch" <(E-Mail Removed)> wrote in message
news:0FE895FF-E3CA-4295-BDDD-(E-Mail Removed)...
> 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

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Matthew Balch" <(E-Mail Removed)> wrote in message
news:0FE895FF-E3CA-4295-BDDD-(E-Mail Removed)...
> 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

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)



"Matthew Balch" <(E-Mail Removed)> wrote in message
news:0FE895FF-E3CA-4295-BDDD-(E-Mail Removed)...
> 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

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

"Bob Phillips" wrote:

> 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)
>
> "Matthew Balch" <(E-Mail Removed)> wrote in message
> news:0FE895FF-E3CA-4295-BDDD-(E-Mail Removed)...
> > 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
>
>
>

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

"Bob Phillips" wrote:

> 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)
>
> "Matthew Balch" <(E-Mail Removed)> wrote in message
> news:0FE895FF-E3CA-4295-BDDD-(E-Mail Removed)...
> > 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
>
>
>

You changed part of the code.

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

Vijay Kotian wrote:
>
> 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
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "Matthew Balch" <(E-Mail Removed)> wrote in message
> > news:0FE895FF-E3CA-4295-BDDD-(E-Mail Removed)...
> > > 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
> >
> >
> >

--

Dave Peterson