Error 1004: Method 'open' of object Workbools failed

bgonen

Active Member
Joined
Oct 24, 2009
Messages
264
From time to time I get a run error 1004:"method 'open' of object 'workbooks' failed and it takes me to the blue shaded code below:
Set thisWB = Workbooks.Open(filename:=sFileName).

When I restart my PC the error disappear but I wonder if someone can help me simplified this code.


Code:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.AskToUpdateLinks = False
Dim mydata As String
 Dim sPath As String
 Dim sFileName As String
 Dim sSheetName As String
 Dim pSheetName As String
 Dim sRange As String
 Dim res As String
Dim lastrow As Long, lastcolumn As Long
'Declare Arrays
 Dim arrWB() As Variant
 Dim thisWB As Workbook
 
'Clear contents in Control file
 
 Sheets("Control").Range("b4:d120, g4:h120").ClearContents
 'Constant Path
 sPath = "C:\VBA\Automating\"
 
'open Data file
 Workbooks.Open "C:\VBA\Automating\Data.xlsm"


'Message;close all workbooks
    MsgBox "Please make sure all forecast models are closed and choose Cell C2 !!"
'In worksheet; forecast
    sSheetName = "Forecast"
    pSheetName = "Prior Forecast"
'message ; what column do you want to paste to ?
    res = InputBox("What column do you wish to paste to")
 
 'Array Models 1 thru 85
arrWB = Array("AFCU-automate.xlsx", "Alaska CU-automate.xlsx", "American Savings Bank-automate.xlsx",)


'When loop thru Array, build up the full file name string
For wbI = LBound(arrWB) To UBound(arrWB)


'Build the Filename
sFileName = sPath & arrWB(wbI)


'open Model wbI
[COLOR=#0000ff][B]        Set thisWB = Workbooks.Open(filename:=sFileName)[/B][/COLOR]




'copy sum of invoice (SAP)
       thisWB.Sheets(sSheetName).Range("A1983:A1983").Copy
       thisWB.Sheets(sSheetName).Range(res & 1983).PasteSpecial xlPasteValues
    
'Copy all automated metrics
       thisWB.Sheets(sSheetName).Range("A1999:b2044").Copy
       thisWB.Sheets(sSheetName).Range(res & 1999).PasteSpecial xlPasteValues
      
       


       
'COPY RESULTS TO CONTROL (the rows.count, 7 for example is for the seventh column on the control sheet)
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 2).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range("I10").Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 3).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 1993).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 4).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 1989).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 7).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 14).Offset(0, 1).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 8).End(xlUp)(2).Value = thisWB.Sheets(pSheetName).Range(res & 14).Offset(0, 1).Value


'Save and Close Model
      Application.DisplayAlerts = False
      thisWB.Save
      thisWB.Close False
      Application.DisplayAlerts = False
    


    Next wbI
    
'Close the Data file without saving
    Application.EnableEvents = False
    Workbooks("Data.xlsm").Close savechanges:=False
    MsgBox "Feed was completed!"
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,214,866
Messages
6,121,996
Members
449,060
Latest member
mtsheetz

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