check for existing worksheet in all open excel files

[Seth]

Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any
open excel file. I would also like my macro to copy this worksheet if it
isn't in the current workbook that the macro was run from. This is the code
I found in the newsgroups so far which is a start. Thanks in advance!

Public Function WSExist(wsname As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Object
On Error Resume Next
WSExist = False
Set objWorksheet = ActiveWorkbook.Sheets(wsname)
If Err = 0 Then WSExist = True
End Function


Sub TestMatrix_Chk()

If WSExist("Test Matrix") = False Then
MsgBox ("No Test Matrix sheet found!")
End If

End Sub

[Tim Barlow]

Seth,

Try:

Sub findTestMatrix()
Dim wb As Workbook
Dim wSht As Worksheet

For Each wb In Workbooks
Debug.Print "Searching: "; wb.Name
If wb.Name <> ThisWorkbook.Name Then
For Each wSht In wb.Worksheets
Debug.Print "Found: "; wSht.Name
If wSht.Name = "TestMatrix" Then
wSht.Cells.Copy
Destination:=ThisWorkbook.Sheets(1).Range("A1")
End If
Next wSht
End If
Next wb

Set wb = Nothing
Set wSht = Nothing

End Sub


The 'debug.print' are just there so you can see what's going on if you step
through it. You may want to change the 'Destination' sheet.

HTH

Tim


"Seth" <[email protected]> wrote in message
news:[email protected]...
> Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in
any
> open excel file. I would also like my macro to copy this worksheet if it
> isn't in the current workbook that the macro was run from. This is the
code
> I found in the newsgroups so far which is a start. Thanks in advance!
>
> Public Function WSExist(wsname As String) As Boolean
> 'returns true if worksheet exists in the active workbook
> Dim objWorksheet As Object
> On Error Resume Next
> WSExist = False
> Set objWorksheet = ActiveWorkbook.Sheets(wsname)
> If Err = 0 Then WSExist = True
> End Function
>
>
> Sub TestMatrix_Chk()
>
> If WSExist("Test Matrix") = False Then
> MsgBox ("No Test Matrix sheet found!")
> End If
>
> End Sub

[Ardus Petus]

Public Function WSExist(wsname As String) As Boolean
'returns true if worksheet exists in the active workbook
Dim objWorksheet As Worksheet
On Error Resume Next
Set objWorksheet = ActiveWorkbook.Worksheets(wsname)
On Error GoTo 0
WSExists = Not objWorksheet Is Nothing
End Function


Sub TestMatrix_Chk()

If WSExist("Test Matrix") = False Then
MsgBox ("No Test Matrix sheet found!")
End If

End Sub

HTH
--
AP

"Seth" <[email protected]> a écrit dans le message de
news:[email protected]...
> Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in
any
> open excel file. I would also like my macro to copy this worksheet if it
> isn't in the current workbook that the macro was run from. This is the
code
> I found in the newsgroups so far which is a start. Thanks in advance!
>
> Public Function WSExist(wsname As String) As Boolean
> 'returns true if worksheet exists in the active workbook
> Dim objWorksheet As Object
> On Error Resume Next
> WSExist = False
> Set objWorksheet = ActiveWorkbook.Sheets(wsname)
> If Err = 0 Then WSExist = True
> End Function
>
>
> Sub TestMatrix_Chk()
>
> If WSExist("Test Matrix") = False Then
> MsgBox ("No Test Matrix sheet found!")
> End If
>
> End Sub

[Seth]

Thanks Tim. I tried the code you posted and got a syntax error for

Destination:=ThisWorkbook.Sheets(1).Range("A1")

Any clue why?

"Tim Barlow" wrote:

> Seth,
>
> Try:
>
> Sub findTestMatrix()
> Dim wb As Workbook
> Dim wSht As Worksheet
>
> For Each wb In Workbooks
> Debug.Print "Searching: "; wb.Name
> If wb.Name <> ThisWorkbook.Name Then
> For Each wSht In wb.Worksheets
> Debug.Print "Found: "; wSht.Name
> If wSht.Name = "TestMatrix" Then
> wSht.Cells.Copy
> Destination:=ThisWorkbook.Sheets(1).Range("A1")
> End If
> Next wSht
> End If
> Next wb
>
> Set wb = Nothing
> Set wSht = Nothing
>
> End Sub
>
>
> The 'debug.print' are just there so you can see what's going on if you step
> through it. You may want to change the 'Destination' sheet.
>
> HTH
>
> Tim
>
>
> "Seth" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in
> any
> > open excel file. I would also like my macro to copy this worksheet if it
> > isn't in the current workbook that the macro was run from. This is the
> code
> > I found in the newsgroups so far which is a start. Thanks in advance!
> >
> > Public Function WSExist(wsname As String) As Boolean
> > 'returns true if worksheet exists in the active workbook
> > Dim objWorksheet As Object
> > On Error Resume Next
> > WSExist = False
> > Set objWorksheet = ActiveWorkbook.Sheets(wsname)
> > If Err = 0 Then WSExist = True
> > End Function
> >
> >
> > Sub TestMatrix_Chk()
> >
> > If WSExist("Test Matrix") = False Then
> > MsgBox ("No Test Matrix sheet found!")
> > End If
> >
> > End Sub
>
>
>

[Dave Peterson]

An unfortunate line break:

wSht.Cells.Copy _
Destination:=ThisWorkbook.Sheets(1).Range("A1")

(notice the spacebar, underscore in the line above)

Seth wrote:
>
> Thanks Tim. I tried the code you posted and got a syntax error for
>
> Destination:=ThisWorkbook.Sheets(1).Range("A1")
>
> Any clue why?
>
> "Tim Barlow" wrote:
>
> > Seth,
> >
> > Try:
> >
> > Sub findTestMatrix()
> > Dim wb As Workbook
> > Dim wSht As Worksheet
> >
> > For Each wb In Workbooks
> > Debug.Print "Searching: "; wb.Name
> > If wb.Name <> ThisWorkbook.Name Then
> > For Each wSht In wb.Worksheets
> > Debug.Print "Found: "; wSht.Name
> > If wSht.Name = "TestMatrix" Then
> > wSht.Cells.Copy
> > Destination:=ThisWorkbook.Sheets(1).Range("A1")
> > End If
> > Next wSht
> > End If
> > Next wb
> >
> > Set wb = Nothing
> > Set wSht = Nothing
> >
> > End Sub
> >
> >
> > The 'debug.print' are just there so you can see what's going on if you step
> > through it. You may want to change the 'Destination' sheet.
> >
> > HTH
> >
> > Tim
> >
> >
> > "Seth" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in
> > any
> > > open excel file. I would also like my macro to copy this worksheet if it
> > > isn't in the current workbook that the macro was run from. This is the
> > code
> > > I found in the newsgroups so far which is a start. Thanks in advance!
> > >
> > > Public Function WSExist(wsname As String) As Boolean
> > > 'returns true if worksheet exists in the active workbook
> > > Dim objWorksheet As Object
> > > On Error Resume Next
> > > WSExist = False
> > > Set objWorksheet = ActiveWorkbook.Sheets(wsname)
> > > If Err = 0 Then WSExist = True
> > > End Function
> > >
> > >
> > > Sub TestMatrix_Chk()
> > >
> > > If WSExist("Test Matrix") = False Then
> > > MsgBox ("No Test Matrix sheet found!")
> > > End If
> > >
> > > End Sub
> >
> >
> >

--

Dave Peterson