Finding a Sheet
 

I'm running an Access Database that creates worksheets in an Excel sheet.
The sheet is automatically named based on one of the criteria from the DB.
The problem I'm having is that I need to check if there is already a sheet in
the workbook with the same name as the one the DB is trying to use. I need
this to be part of the DB's coding, but can't seem to figure out a chunk of
code to to find the name of a worksheet. Any thoughts out there? I tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff

Finding a Sheet
 

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("Name_of_Sheet")
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox "Duplicate Sheet"
Else
'Do Stuff
End If


In article ,
JC wrote:

I'm running an Access Database that creates worksheets in an Excel sheet.
The sheet is automatically named based on one of the criteria from the DB.
The problem I'm having is that I need to check if there is already a sheet in
the workbook with the same name as the one the DB is trying to use. I need
this to be part of the DB's coding, but can't seem to figure out a chunk of
code to to find the name of a worksheet. Any thoughts out there? I tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff

Finding a Sheet
 

This just prompts a "Run-time error '9': Subscript out of range" error.

- Jeff

"JE McGimpsey" wrote:

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("Name_of_Sheet")
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox "Duplicate Sheet"
Else
'Do Stuff
End If


In article ,
JC wrote:

I'm running an Access Database that creates worksheets in an Excel sheet.
The sheet is automatically named based on one of the criteria from the DB.
The problem I'm having is that I need to check if there is already a sheet in
the workbook with the same name as the one the DB is trying to use. I need
this to be part of the DB's coding, but can't seem to figure out a chunk of
code to to find the name of a worksheet. Any thoughts out there? I tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff

Finding a Sheet
 

Replace

"Name_of_Sheet"

with your proposed worksheet name.

In article ,
JC wrote:

This just prompts a "Run-time error '9': Subscript out of range" error.

- Jeff

"JE McGimpsey" wrote:

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("Name_of_Sheet")
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox "Duplicate Sheet"
Else
'Do Stuff
End If


In article ,
JC wrote:

I'm running an Access Database that creates worksheets in an Excel sheet.

The sheet is automatically named based on one of the criteria from the
DB.
The problem I'm having is that I need to check if there is already a
sheet in
the workbook with the same name as the one the DB is trying to use. I
need
this to be part of the DB's coding, but can't seem to figure out a chunk
of
code to to find the name of a worksheet. Any thoughts out there? I
tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff

Finding a Sheet
 

....um... yeah, I did that, not that green to all of this.

Don't worry about replying. I'll figure it out on my own.

- Jeff

"JE McGimpsey" wrote:

Replace

"Name_of_Sheet"

with your proposed worksheet name.

In article ,
JC wrote:

This just prompts a "Run-time error '9': Subscript out of range" error.

- Jeff

"JE McGimpsey" wrote:

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("Name_of_Sheet")
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox "Duplicate Sheet"
Else
'Do Stuff
End If


In article ,
JC wrote:

I'm running an Access Database that creates worksheets in an Excel sheet.

The sheet is automatically named based on one of the criteria from the
DB.
The problem I'm having is that I need to check if there is already a
sheet in
the workbook with the same name as the one the DB is trying to use. I
need
this to be part of the DB's coding, but can't seem to figure out a chunk
of
code to to find the name of a worksheet. Any thoughts out there? I
tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff

Finding a Sheet
 

Don't get rid of those error checking lines that JE added, too.

With "on error resume next" there, you shouldn't have gotten the error on that
section of code.

JC wrote:

...um... yeah, I did that, not that green to all of this.

Don't worry about replying. I'll figure it out on my own.

- Jeff

"JE McGimpsey" wrote:

Replace

"Name_of_Sheet"

with your proposed worksheet name.

In article ,
JC wrote:

This just prompts a "Run-time error '9': Subscript out of range" error.

- Jeff

"JE McGimpsey" wrote:

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("Name_of_Sheet")
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox "Duplicate Sheet"
Else
'Do Stuff
End If


In article ,
JC wrote:

I'm running an Access Database that creates worksheets in an Excel sheet.

The sheet is automatically named based on one of the criteria from the
DB.
The problem I'm having is that I need to check if there is already a
sheet in
the workbook with the same name as the one the DB is trying to use. I
need
this to be part of the DB's coding, but can't seem to figure out a chunk
of
code to to find the name of a worksheet. Any thoughts out there? I
tried
the below, but that didn't work.

Sheets("Name_of_Sheet").Count

- Jeff



--

Dave Peterson

Finding a Sheet
 

Hard to tell how green one is from a ng post.

I suspect you didn't add the On Error Resume Next code, since you
shouldn't have gotten a Subscript Out Of Range if you had.

If you did, I can only conclude that the error is elsewhere in your code.


In article ,
JC wrote:

...um... yeah, I did that, not that green to all of this.

Don't worry about replying. I'll figure it out on my own.