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]

Yes I can.

 

[dpaton05]

Both lines of code look the same to me !!
Did you take Nories advice of moving the file to say the C:\ Drive??

I did and I get the same results.

 

[Michael M]

Can you upload your latest workbook ??

 

[dpaton05]

I am not at my computer at the moment but I will upload it as soon as I can.

 

[dpaton05]

YAY!! The net is working again. Here it is.

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

Please note, it works on my home pc but it won't work on my laptop or when I get to work. Very confusing.

Thanks guys,
Dave

 

[Michael M]

The code works fine for me and opened the file instantly!!
Are you using the flash drive at home AND at work ??

I'd suggest either the "ThisWorkbook.Path" is wrong when you are at work. If you are on a server, there might be access restrictions for certain drives.

OR

The required file is not in the directory you are targeting with Thisworkbook.path

 

[dpaton05]

Yes, I am using the same flash drive at home and at work. All the files are stored in the one folder on the flash drive. Is there an alternative way I can write ThisWorkbook.Path to get the same results?

 

[Michael M]

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 !

 

[dpaton05]

I think it might be something to do with permissions as it won't work while I am at work but it works at home. Is there some part of the code of cmdCopy that requires administrative privileges to run?

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
            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

 

[Michael M]

Try turning changing enableevents=true, otherwise I don't see a problem with the code !!
Have you copied the files to a different computer at work and tried it ??