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?

 

[Michael M]

@dpaton05
Did you try my latest line of code ??
@Norie
When I run the code the file extension is visible....xlsm !

 

[dpaton05]

DocYearName has the value 2018 - 2019 NPSS Work Allocation Sheet.xlsm and it is the appropriate extension, otherwise, the error would say subscript out of range. Possibly, it doesn't like that method of opening the workbook?

 

[Michael M]

Did you try this line as mentioned previously

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

 

[Michael M]

Dave
Here's another stab....
Have a look at File>>Options>>Trust Center....click on Trust center Settings and then on Trusted Locations....then tick the box that says "Allow trusted locations" at the bottom of the RH window.....OR add a new trusted location

 

[dpaton05]

Don't use the flash drive !!
Copy the files to a directory on the server instead !!
Making sure of course that the drive on the server is accessible !

Thanks Michael, I never thought of that. I am still to try it at work but it worked at home

 

[dpaton05]

Dave
Here's another stab....
Have a look at File>>Options>>Trust Center....click on Trust center Settings and then on Trusted Locations....then tick the box that says "Allow trusted locations" at the bottom of the RH window.....OR add a new trusted location

Thanks for the idea Michael, I will try this one when I get to work, which won't be for a week as I am away from work all next week.

 

[dpaton05]

Dave
Here's another stab....
Have a look at File>>Options>>Trust Center....click on Trust center Settings and then on Trusted Locations....then tick the box that says "Allow trusted locations" at the bottom of the RH window.....OR add a new trusted location

I tried that Michael but it still didn't work. It still gives the error. I tried to copy 3 rows at once to a blank allocation sheet and it displays the error but I ended the procedure and looked at the quoting tool and it had copied one row successfully of the 3 rows in the costing tool.

I tried to copy just one row from the costing tool and it didn't give me any error so I think it may be something with the code to copy multiple lines.

One thing I did notice besides the above issue, when I try to open the allocation sheet after I have copied information to it, I get the error "We found a problem with some content in xxx" just like it has become corrupted. I tried to make a new workbook and put the information back into it and tried to open it before I copied anything to them and it worked fine but as soon as i pressed the button to copy rows to them, it started making the error. That tells me there must be something causing it in the code. I have uploaded the latest version if you could look at the code please as I have no idea what the problem may be.

https://www.dropbox.com/s/tpfm711tjasho0z/quoting tool 11.1 WCI.xlsm?dl=0

Thanks Michael,
Dave

 

[Michael M]

OK, which sheet is the allocation sheet ??
Which code is giving the problem ??
Does this copy / paste involve merged cells ?/

 

[dpaton05]

OK, which sheet is the allocation sheet ??
Which code is giving the problem ??
Does this copy / paste involve merged cells ?/

Before I get to your questions, something else I have noticed. If I copy a row to the allocation sheet, save and close it, then try to open it again, I get an error saying "we found a problem with some content in excel...." just like it is corrupted. If I delete the row that was previously copied in so there are no rows in the sheet, save and close it and try again, the error is gone.


If I have one row in the quoting tool and copy it to the costing tool, I can then press the button to copy it to the allocation sheet and it works. If I add a row to the costing tool and try again, it gives me the method failed error. If I delete the row so there is only one row left again in the costing tool, it shows the error.

To make it work again, I need to delete all the rows in the costing tool, make sure there is only one row in the quoting tool and copy the one row to the costing tool. I can then copy it to the allocation sheets without any error.

The allocation sheet is a separate, financial year document that the button on the costing tool sends the information to. I have monthly sheets on the allocation sheet and the rows get pasted into the monthly sheet, according to the date.

There is no merged cells involved in the copy/paste process.

Here is my copy procedure, I have underlined and made bold the line of code that is highlighted:

Sub cmdCopy()

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
        
        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
        '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[U][B]
            Workbooks.Open (ThisWorkbook.Path & "\" & DocYearName)[/B][/U]
            
            Set wsDst = Workbooks(DocYearName).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 Workbooks(DocYearName).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
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        'End If

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

I am going to have a stab in the dark, I think the above code is somehow putting something in the worksheet to make it appear like it is corrupted.

 

[dpaton05]

Some additional info:

All the workbooks are stored in the same folder and the variable DocYearName correctly identifies the workbook the the row needs to be copied to.