Syntax error? 2

[fredmartinmaine]

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?

 

[SkipVought]

Hi,

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[fredmartinmaine]

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

 

[strongm]

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 [tt]Exit For[/tt] just before the [tt]Case "QAReviewReport"[/tt] 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"?

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[fredmartinmaine]

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

 

[SkipVought]

Springer to Katahdin works better than Katahdin to Springer. ;-) ⛰

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]