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 think I found an answer to part of the problem but I don't know how to fix it. I commented out the line of code that opens the workbook

Workbooks.Open ThisWorkbook.Path & "" & DocYearName

and I opened the workbook manually, performed the copy and it worked. Therefore, I think it is something wrong with that line of code.

 

[Michael M]

that line doesn't have a back slash

Workbooks.Open ThisWorkbook.Path & [color=red]"\"[/color] & DocYearName

 

[dpaton05]

I don't know what happened as my file at work has the back slash in it already but my file that I took home didn't have it. This is the code from my from my file at work. I think it may be the same but I am not sure as I have been trying to work out the issue for a while.

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 of the date within 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

            Workbooks.Open (ThisWorkbook.Path & "\" & DocYearName)
            
            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 still getting the error, "method open of object workbooks failed" with the same line of code:

Workbooks.Open (ThisWorkbook.Path & "\" & DocYearName)

 

[dpaton05]

Maybe, is that line of code in the wrong spot to open the appropriate sheet for each line and perform the copy/paste process?

 

[dpaton05]

I have also tried the open and repair option to repair the file as I read that it may be corrupted but that didn't work either.

 

[dpaton05]

It is really strange as it used to work and I don't think I changed anything and then it stopped working, by giving me the error.

 

[dpaton05]

I think I have found the problem. The file that the rows are being copied to, keeps getting corrupted. I open and repair the file and it works fine. Is there a way to use vba to open and repair the file if it is corrupt when the file is meant to be opened, so that the whole process could be automated?

Would there be anything in the code that keeps corrupting the file?

 

[Michael M]

Might pay to have a read here

https://www.spreadsheet1.com/how-to-diagnose-excel-file-corruption-and-repair-workbooks.html

Also make sure the file that is getting corrupted, is being saved correctly.
If you step through the code 1 line at a time, does the file get corrupted ??
If not, you may have to put a wait line in to slow the code down !!!

 

[dpaton05]

Thanks for that Michael, yes, I only just found out today that the file had become corrupted. Lucky I had been saving versions as I have been going along so I have found a previous version that works in which there will not be too much work to get back to where I was up to.

My supervisor had told me not to work on it at home. I read that different office versions can cause corruption so now I think I will only work on it at work. Thanks for helping me with that Michael.

 

[Norie]

Do you know why/how the file is becoming corrupted?