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]

Do you mean that I should not copy anything? I have a lot of previous versions. Do you think I could try an earlier version first?

 

[Michael M]

Why not simply make a copy of the latest version, and use that ??
AND
Now might be a good time for a cleanout !!!...you have about 8 modules that could be removed.
quite a few macros that could be placed in one module, and a heap of code in Sheet modules that should be in Standard modules......

 

[DanteAmor]

Do you mean that I should not copy anything? I have a lot of previous versions. Do you think I could try an earlier version first?

You can copy the information, select the first sheet, select all the cells and copies, in the new book on the first sheet, in cell A1 you paste.


And so you do it sheet by sheet.


Something in your book was damaged, that's why you should only recover the data, not the complete sheets.

 

[dpaton05]

It is really strange that the file works on my home pc but won't work on the computer I use at work.

 

[Michael M]

It also seems odd that we can make it work at different sites.........BUT......you are working with 2 different versions of Excel !!!

 

[dpaton05]

Sorry Dante, I went to work today but thought that my problem was with the quoting tool, not the allocation sheets, so I never thought of trying the new allocation sheet you sent me. I will try that new file you sent me when I am next at work, so Friday. Thanks guys for helping me with this issue.

 

[DanteAmor]

I hope everything goes well

 

[dpaton05]

Got another problem.

I have this code to delete a selected row but if you delete all the rows, it deletes the formulas at the end of the table. These formulas are required to make it work. I need code to not delete the formulas if it has only one row left. Here is the code that I have:

Private Sub cmdDelSelectRow_Click()

ActiveSheet.Unprotect Password:="npssadmin"
    Dim rng As Range
    
    On Error Resume Next
    With Selection.Cells(1)
        Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
        On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "Please select a cell within a row that you want to delete.", vbCritical
        Else
            rng.Delete xlShiftUp
        End If
    End With
    
'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"

Application.EnableEvents = True
'ActiveSheet.Protect Password:="npssadmin"
End Sub

I am still getting random errors. By that I mean, sometimes I will get errors and sometimes I won't. Should I convert the allocation sheets (where the rows are being copied too), to be a table in each sheet as the conversion works pretty well to go from the quoting tool to the costing tool, and they are both tables?

 

[dpaton05]

One of the errors is sub script out of range. This is the highlighted line of code:

 Set wsDst = Workbooks(DocYearName).Worksheets(Combo)

If I hover over it, DocYearName is "2018 - 2019 NPSS Work Allocation Sheet.xlsm" and if I hover over over Combo I get the July, but both files are stored in the same folder.

 

[Michael M]

Try something like this
AND
with regard to converting to tables, I reckon in your case, tables have been a causing a lot of the problems !
Personally, I wouldn't have used them for the processes involved in your workbook.....but that's a personal choice on my part...

Private Sub cmdDelSelectRow_Click()

ActiveSheet.Unprotect Password:="npssadmin"
    Dim rng As Range
    
    On Error Resume Next
    With Selection.Cells(1)
        Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
        On Error GoTo 0
        If rng Is Nothing Then
            MsgBox "Please select a cell within a row that you want to delete.", vbCritical
        Else
         [color=red]If rng.HasFormula = False Then rng.Delete[/color]
        End If
    End With
    
'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"

Application.EnableEvents = True
'ActiveSheet.Protect Password:="npssadmin"
End Sub