method open of object workbooks failed

[dpaton05]

While working on a spreadsheet at work, I was getting the error "method open of object workbooks failed", but when I got home, the same file was not giving me any errors. Any ideas why?

 

[dpaton05]

I copied the quoting tool file and the file that the rows get transferred too, onto a common drive on the server that everyone has access to and it does the same thing.

 

[Michael M]

Did you change the code, as suggested ??
AND
Do the variables reflect the correct locations on that common drive ?
If there is still a problem, I'd talk to your IT !

 

[Michael M]

Is your laptop and work using the same versions of windows AND excel ??

 

[dpaton05]

1. Work has Microsoft office professional plus 2016 and I have Microsoft office 365 but I thought that they would be compatible.
2. They both are running windows 10
3. I tried to comment out the code enableevents=true but that did nothing.
4. At the end, I noticed that the code in the With Application is slightly different to the code within the With Application at the beginning, would that make a difference?
5. They appear to reflect the locations on the network drive.

 

[Michael M]

I can only suggest then that the versions have a compatibility problem.
Another thing to try would to move the code out of the sheet module.....it shouldn't be there anyway !!...put it in the Thisworkbook module
AND
Might be meaningless, but change the line of code to

Workbooks.Open Filename:=Activeworkbook.Path & "\" & DocYearName

Check with IT to see if there have been any addins installed, and if so get them turned off and retest....otherwise I can' really help you !!

 

[dpaton05]

Thanks for trying Michael.

I tried to move it out and put it in the thisworkbook module and then I got an error with the selection of the button that called the procedure and it says sub or function not defined.

 

[dpaton05]

I just copied cmdCopy into the thisworkbook module and in the sheet 10 module, I included call cmdCopy in the code and that was where it said the sub was not defined. What else do I need to do?

 

[Norie]

Does DocYearName include the file extension?

Does this work any better?

Option Explicit

Sub cmdCopy()
Dim wbDst As Workbook
Dim wsDst As Worksheet, wsSrc As Worksheet, tblrow As ListRow
Dim Combo As String, sht As Worksheet, tbl As ListObject
Dim LastRow As Long, lr As Long, DocYearName As String
Dim WbName As String, Workbook As Workbook
    With Application
    
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With


    'assign values to variables
    Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")

    For Each tblrow In tbl.ListRows
        If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
            MsgBox "The Date, Service or Requesting Organisation has not been entered for every record in the table"
            Exit Sub
        End If
        
        'For every row, set value of combo to the name of the month that contains the date of the row
        Combo = tblrow.Range.Cells(1, 26).Value

        If tblrow.Range.Cells(1, 6).Value = "Ang Wes" Then
            DocYearName = tblrow.Range.Cells(1, 37).Value
        Else
            DocYearName = tblrow.Range.Cells(1, 36).Value
        End If

        'If Workbooks(ThisWorkbook.Path & "\" & DocYearName).Open = True Then
        'Workbooks(ThisWorkbook.Path & "\" & DocYearName).Close
        'Else
        'To open the workbook stored in the variable DocYearName
        
        Set wbDst = Workbooks.Open(ThisWorkbook.Path & "\" & DocYearName)

        Set wsDst = wbDst.Worksheets(Combo)
        
        lr = wsDst.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
        
        With wsDst
            'This copies the first 10 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
            tblrow.Range.Resize(, 10).Copy
            'This pastes in the figures in the first 10 columns starting in column A
            .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
            'Overwrites the numbers pasted to column I with a formula
            .Range("I" & .Range("I" & .Rows.Count).End(xlUp).Row).Formula = "=IF(R[0]C[-4]=""*Activities"",0,RC[-1]*0.1)"
            'Overwrites the numbers pasted to column J with a formula
            .Range("J" & .Range("J" & .Rows.Count).End(xlUp).Row).Formula = "=IF(R[1]C[-5]=""*Activities"",RC[-2],RC[-1]+RC[-2])"
            'sort procedure copied from vba
            wsDst.Sort.SortFields.Clear
            wsDst.Sort.SortFields.Add Key:=Range("A4:A" & lr), _
                                      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With wbDst.Worksheets(Combo).Sort
                .SetRange Range("A3:AK" & lr)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            
        End With
        
        'save and close the workbook
        wbDst.Close SaveChanges:=True
        'End If

    Next tblrow

    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    
End Sub

 

[dpaton05]

I still get the same error, "method open of objects workbooks failed" with that code Norie with the following line of code highlighted:

Set wbDst = Workbooks.Open(ThisWorkbook.Path & "\" & DocYearName)

 

[Norie]

What is the exact value of DocYearName?

Does it include the appropriate file extension e.g. xlsx, xlsm?