Method Open of object workbooks failed

[ezonemy]

Hi im using the below VBA code, however in one of my staff's pc (XP, Office 2010) it shows this error msg 'METHOD OPEN OF OBJECT WORKBOOKS FAILED'

Sub Copydata3()
  On Error GoTo ErrorChk
ErrorChk: MsgBox Err.Description
    Application.ScreenUpdating = False
    Dim foundDate As Range
    Dim SourceWB As Workbook
    Set SourceWB = ActiveWorkbook
    Workbooks.Open ("[URL="file://\\IP ADDRESS\FOLDER\TEST"]\\IP ADDRESS\FOLDER\TEST[/URL].xlsx"), Password:="TEST"
    Dim ws As Worksheet
    For Each ws In Workbooks("TEST.xlsx").Sheets
        If ws.Name = SourceWB.Sheets("SCORE SHEET").Range("C3").Value Then
            Set foundDate = ws.Range("A:A").Find(SourceWB.Sheets("SCORE SHEET").Range("H3"), LookIn:=xlValues, lookat:=xlWhole)
            If Not foundDate Is Nothing Then
                foundDate.Offset(0, 9) = SourceWB.Sheets("SCORE SHEET").Range("J2")
            End If
        End If
    Next ws
    Workbooks("TEST.xlsx").Close True
    Application.ScreenUpdating = True
End Sub

Only this pc givin this error msg, other staff have no issue. Please help.

 

[dmt32]

Hi,
when working with files across a network it’s best to first check that you have access to the required file.

Not tested but see if this update to your code helps which includes a common Function to check file exists.

Adjust code as required to meet specific project need.

 Sub Copydata3()

    Dim foundDate As Range
    Dim SourceWB As Workbook
    Dim wsScoreSheet As Worksheet
    Dim sPath As String, sFileName As String


    sPath = "\\IP ADDRESS\FOLDER\"
    sFileName = "TEST.xlsx"
    
    On Error GoTo ErrorChk
    Set wsScoreSheet = ThisWorkbook.Sheets("SCORE SHEET")
    
    If FileExists(sPath & sFileName) Then
        Application.ScreenUpdating = False
        Set SourceWB = Workbooks.Open(sPath & sFileName, ReadOnly:=False, Password:="TEST")
        If Not SourceWB Is Nothing Then
            Dim ws As Worksheet
            For Each ws In SourceWB.Sheets
                If ws.Name = wsScoreSheet.Range("C3").Value Then
                    Set foundDate = ws.Range("A:A").Find(wsScoreSheet.Range("H3"), LookIn:=xlValues, lookat:=xlWhole)
                    If Not foundDate Is Nothing Then
                        foundDate.Offset(0, 9) = wsScoreSheet.Range("J2")
                    End If
                End If
            Next ws
            SourceWB.Close True
        End If
    Else
        MsgBox sFileName & Chr(10) & "File Not Found", 48, "Not Found"
    End If
    
    Set SourceWB = Nothing


ErrorChk:
    Application.ScreenUpdating = True
    If Not SourceWB Is Nothing Then SourceWB.Close False
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub




Function FileExists(ByVal FileName As String) As Boolean
    'stock function
    FileExists = Not Dir(FileName, vbDirectory) = vbNullString
End Function

Hope Helpful

Dave

 

[ezonemy]

Dear Dave, tried ur code however still the problem persist. The pc actually running on XP and EXCEL 2007. The file is accessible from the pc. The code above did not mention file not found but again showing "method open of object workbooks failed".

Clueless on this problem. Hope someone could assist.