×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Syntax error?
2

Syntax error?

Syntax error?

(OP)
I'm getting a runtime error from this code.

It deals with Excel documents that either have a worksheet named "Current" or a worksheet named "QAReviewReport".

I'm opening the Excel document 'src_file' read Only. That works.

Then , if there is a worksheet named "Current" it activates. That works ok too.

However, if there's any error opening the "Current" worksheet, for example if it doesn't exists, I want to try to open a worksheet called "QAViewReport" instead. After I added the IF statement, it started failing. Here's the partial code:

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file,,TRUE)

oBook.Worksheets("Current").Activate
If Err.Number <> 0 Then
oBook.Worksheets("QAReviewReport").Activate
End If

If neither exists I'll accept a runtime error. Again, it works fine if there is a "Current" worksheet, and if I comment out the If / End If lines.

I'm hoping this is just a syntax error. Any ideas?

RE: Syntax error?

Hi,

CODE

Dim oBook, oSheet
Set oBook = oExcel.Workbooks.Open(src_file,,TRUE)
For Each oSheet in oBook.Sheets
   Select Case oSheet.Name 
      Case “Current” 
         ‘Perform here for sheet Current
      Case “QAReviewReport”
         ‘Perform here for sheet QAReviewReport
   End Select
Next 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Syntax error?

(OP)
Regarding that case statement - does it mean "if the active sheet is A, then do this, but if the active sheet is B, then do that" ? I'm not sure that will work.

For example, the doc may have been saved with some unknown sheet active, but still contain the sheet I want.

I'm trying to make either one or the other sheet active, trying "Current" first, then trying "QAReviewReport", then failing if neither exist.

Fred

RE: Syntax error?

No, that's not what Skip's code is doing. On the other hand, it doesn't quite do what you were asking either (which can be mostly fixed by sticking a somewhat inelegant Exit For just before the Case "QAReviewReport" line, but is somewhat dependant on the sheet tab order) . On the other hand, your code should be doing what you suggest - assuming that you have a relevant On Error Resume Next in scope. SO what error message are you getting when it "started failing"?

RE: Syntax error?

CODE

Dim oBook, oSheet, bFound
Set oBook = oExcel.Workbooks.Open(src_file,,TRUE)
bFound = False
For Each oSheet in oBook.Sheets
   Select Case oSheet.Name 
      Case “Current” 
         oSheet.Activate
         bFound = True
         Exit For
   End Select
Next 

If Not bFound Then
   For Each oSheet in oBook.Sheets
      Select Case oSheet.Name 
         Case “QAReviewReport” 
            oSheet.Activate
            bFound = True
            Exit For
      End Select
   Next 
End If

If bFound Then
   ‘The correct sheet has been activated
Else
   ‘Neither sheet is in this workbook
End If 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Syntax error?

(OP)
Thanks, yes I was beginning to realize that the issue wasn't the syntax of the If statements but was relate to error handling. I'm liking the code above, which avoids expecting errors. I like that better.
Fred

RE: Syntax error?

Springer to Katahdin works better than Katahdin to Springer. winky smile

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close