method open of object workbooks failed

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,362
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Where were you trying to open the workbook from....it could be an access or server issue ??
Is it a shared workbook ?
 
Upvote 0
The workbooks are all stored in one folder on my flash drive. I would post the code but I am on my mobile as my internet connection is down and I have no way of getting the code from my workbook to my phone, short of typing it all in and I don't feel like doing that.

As I said, I have no errors at home but at work it was giving me the error with this one line of code:

Workbooks.open (thisworkbook.path & "" & docyearname)

Docyearname is the variable that contains the name of the workbook that needs to be opened.

Not sure if this would have anything to do with it but the internet connection was down at my work today.

Thanks Michael,
Dave
 
Upvote 0
well...it depends on whether the internet is required to open the file...the server at work might have problems if the net is down !!
Check to see what happens when the net is back up !!!
 
Upvote 0
The internet is not required to open the file, it is stored on my flash drive.
 
Upvote 0
Yay, the internet is working again. Here is the code.

Code:
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
 
Upvote 0
Some extra info:


  • The variable DocYearName stores the name of the correct file to put the rows in.
  • Combo stores the name of the sheet that it needs to go in.
  • They are all stored in the same folder.
 
Upvote 0
Code:
If Workbooks(ThisWorkbook.Path & "" & DocYearName).Open = True Then

and it says subscript out of range.
 
Last edited:
Upvote 0
So, when you step through and get to this line, hover the mouse over the DocYearName OR ThisWorkbook.Path and see what it says !!
If either is not correct , there's your problem

Code:
If Workbooks(ThisWorkbook.Path & "" & DocYearName).Open = True Then
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top