3

I have a function that looks for open workbooks and pulls them into a master workbook. It works just fine when I'm importing a workbook created with a modern version of office, but it doesn't seem to detect workbooks that open in compatibility mode. Only the left 24 characters are constant for the workbook in question.

For a wide variety of reasons I've gone over in other posts downloading the workbook in question is not an option.

Here is the function.

Public Sub FindReport()

Debug.Print "Finding Report"
On Error GoTo Failed
Dim rName() As String
Dim wb As Workbook
Dim tWb As Workbook

rName(0) = "Case Detail"
rName(1) = "Disability_Claim_Status_"
'rName(2) = "placeholder"

For Each wb In Workbooks

'This line gives no output when I have the function try to find a workbook
'that has opened in compatibility mode
Debug.Print wb.Name
    If Left(wb.Name, 11) = rName(0) Then
        Set tWb = wb
        ImportReport tWb
        tWb.Close
        CaseFAS
        Exit For
    End If
    If Left(wb.Name, 24) = rName(1) Then
        Set tWb = wb
        ImportReport tWb
        tWb.Close
        'CaseFAS
        Exit For
    End If
    'If Left(wb.Name, 11) = rName(2) Then
    '    Set tWb = wb
    '    ImportReport tWb
    '    tWb.Close
    '    'CaseFAS
    '    Exit For
    'End If
Next wb
Failed:
End Sub

EDIT for clarification:

I had another version of this code that pulled in a specific worksheet that opens from a website, now that I need to expand it to handle another sheet I modified the declarations accordingly, and screwed up declaring the array.

Rational Redneck
  • 121
  • 1
  • 9
  • 4
    Because of `On Error GoTo Failed` just jumping to `End Sub` if an error occurs you will not get notified! If you don't see the error message you don't know what's going on. Silencing error messages is a very bad idea, make sure you always get notified of errors (see [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling)). Remove `On Error GoTo Failed` to see what errors you get in case of your compatibility workbooks. – Pᴇʜ May 21 '21 at 07:25
  • 1
    turns out I somehow screwed up declaring the string array.......... compatibility mode wasn't an issue at all – Rational Redneck May 21 '21 at 07:49
  • I saw that but I thought the effect you figuring that out yourself would be more effective ^^ So you can write an answer for your own question, if you like. – Pᴇʜ May 21 '21 at 07:50
  • I completely forgot i had that error statement in the code. I put that in awhile ago when i had some debug stuff after the failed statement. I took out the debug statements once I got it working, but never took out the error handling. – Rational Redneck May 21 '21 at 07:53

1 Answers1

1

Before assuming something complicated is the problem make sure you didn't screw up something basic!

Here is the answer to my question....

Declare and Initialize String Array in VBA

Rational Redneck
  • 121
  • 1
  • 9