Object invoke error ... sometimes

[pgag45]

Hello all,

I'm experiencing a rather frustrating error in both Excel VBA 2003/2007. It only happens sometimes (somewhat rarely), during the below code. I have 5 necessary references, which I can list if need be. Having read the MS help ( http://support.microsoft.com/default...;en-us;Q319832 ) on this topic I feel I may need to "fully qualify" my object references in the below code, but don't really know how to go about doing that...

Here's the code w/ the problem. The below code essentially opens and copies a worksheet from a workbook into the active workbook, then closes the workbook.

Application.DisplayAlerts = False
Dim sfilename As String
sfilename = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="N-INDEX")
If sfilename = "False" Then Exit Sub
Dim temp As String
temp = spliceFileNameEnd2(sfilename)
Dim shtnext As Variant
For Each shtnext In Sheets
    If shtnext.name = "pindexCA" Then 'Search/Delete charts w/ same name
        Sheets("pindexCA").Visible = True
        Application.DisplayAlerts = False 'No delete prompt
        Sheets("pindexCA").Delete
    End If
Next shtnext
Workbooks.Open FileName:=sfilename
Dim found As Boolean
found = False
For Each shtnext In Sheets
    If shtnext.name = "pindexCA" Then
        found = True
        Exit For
    End If
Next shtnext
If found = False Then
    Workbooks(temp).Close SaveChanges:=False
    MsgBox ("No P-Index File was found in that file.")
    Sheets.Add
    ActiveSheet.name = "pindexCA"
    Sheets("pindexCA").Visible = xlVeryHidden
    Exit Sub
End If
Sheets("pindexCA").Copy After:=Workbooks(ThisWorkbook.name).Sheets(ThisWorkbook.Sheets.count)
Workbooks(temp).Close SaveChanges:=False
Sheets("pindexCA").Visible = xlVeryHidden
Sheets("Driver").Activate
Application.DisplayAlerts = True

[westconn1]

Workbooks.Open FileName:=sfilename

i would set the open workbook to an object
set objwb = Workbooks.Open FileName:=sfilename

you can then use the object to know you are working with the correct workbook

For Each shtnext In objwb.Sheets
    If shtnext.name = "pindexCA" Then
        found = True
        Exit For
    End If
Next shtnext

if the loop completes shtnext will be empty, but if the sheet is found and exit for is executed shtnext will still contain the worksheet object and you can work with that
if shtnext is declared as a worksheet, you can test if it is nothing instead of using a boolean to know if it was found

same when you add a sheet, don't rely on using the active sheet

set newsht = Sheets.Add
    newsht.name = "pindexCA"
    newsht.Visible = xlVeryHidden

[pgag45]

i would set the open workbook to an object
set objwb = Workbooks.Open FileName:=sfilename

Thanks for the reply... As I am trying to stay "fully qualified" is there a difference b/w declaring the above "objwb" as a workbook or excel.workbook?

Can you explain why the error is happening in the first place? References have the method name conflicts or something?

Thanks!

[anhn]

This gives you clearer image:

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim sh As Object
    
    Dim sfilename As String
    Dim temp As String
    Dim shtnext As Variant
    Dim found As Boolean
    
    sfilename = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="N-INDEX")
    If sfilename = "False" Then Exit Sub
    
    Set wb2 = Workbooks.Open(Filename:=sfilename)
    
    On Error Resume Next
    Set sh = wb2.Sheets("pindexCA")
    On Error GoTo 0
    If Not sh Is Nothing Then
        wb2.Close SaveChanges:=False
        MsgBox ("No P-Index File was found in that file.")
        Exit Sub
    End If
    
    Set wb1 = ThisWorkbook
    
    On Error Resume Next
    Application.DisplayAlerts = False
    wb1.Sheets("pindexCA").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    sh.Copy After:=wb1.Sheets(wb1.Sheets.Count)
    wb2.Close False
    
    wb1.Sheets(wb1.Sheets.Count).Visible = xlVeryHidden
    wb1.Sheets("Driver").Activate

[pgag45]

thanks anhn this seems to be working well... just changed

If Not sh Is Nothing Then
 
to
 
If sh Is Nothing Then

I don't need anything like set wb2 = nothing at the end?

Thanks much all!

[pgag45]

I set sh, wb1, wb2 all to nothing at the end of the code... but still getting object invoked error.

Quite frustrating error. Will work usually on first 2-3 times of running code then crashes with that error.. Any more help?

[pgag45]

My updated code is ...

[pgag45]

Public Function openIndexFile(sheetName As String) As Boolean
DoEvents
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sh As Object
 
Dim sfilename As String
Dim temp As String
Dim shtnext As Variant
Dim found As Boolean
 
Set wb1 = ThisWorkbook
 
For Each shtnext In Sheets
    If shtnext.name = sheetName Then
        Sheets(sheetName).Visible = True
        Application.DisplayAlerts = False
        Sheets(sheetName).Delete
        Exit For
    End If
Next shtnext
    
sfilename = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="N-INDEX")
If sfilename = "False" Then Exit Function
 
Set wb2 = Workbooks.Open(FileName:=sfilename)
 
On Error Resume Next
Set sh = wb2.Sheets(sheetName)
On Error GoTo 0
If sh Is Nothing Then
    wb2.Close SaveChanges:=False
    MsgBox ("No " & sheetName & " was found in that file.")
    openIndexFile = False
    Exit Function
End If
 
 
On Error Resume Next
Application.DisplayAlerts = False
wb1.Sheets(sheetName).Delete
On Error GoTo 0
 
sh.Copy After:=wb1.Sheets(wb1.Sheets.count)
wb2.Close False
Set wb2 = Nothing
 
wb1.Sheets(sheetName).Visible = xlVeryHidden
wb1.Sheets("Driver").Activate
Application.DisplayAlerts = True
openIndexFile = True
Set wb1 = Nothing
Set sh = Nothing
End Function

[westconn1]

which line causes the error?

you need some way to find out what is actually causing error, put some debug.print statements so you can tell when it crashes