Error 1004 File could not be found

bgonen

Active Member
Joined
Oct 24, 2009
Messages
264
The code below loop thru 80 files. It worked perfectly until I pulled one of the file (I saved it later on a different directory) but I still get this error:
C:\VBA\Auotomating\First Citizens.xlsx could not be found..
Eventually I deleted the file that I initially pulled but I still get this error.


Code:
Sub Both_Feed_PullResults_FULL_LONG_version() 'LONG version 5.17.15


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"
'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", _
"Ameriprise-automate.xlsx", "AMEX-automate.xlsx", "BancorpSouth-automate.xlsx", "Bank Hapoalim-automate.xlsx", _
"Bank Leumi-automate.xlsx", "Bank of China-automate.xlsx", "Bank of Stockton-automate.xlsx", _
"Bank of the West-automate.xlsx", "Bank United-automate.xlsx", "BB&T-automate.xlsx", "BECU-automate.xlsx", _
"Bell State Bank and Trust-automate.xlsx", "California CU-automate.xlsx", "Capital One-automate.xlsx", "Capitol Federal-automate.xlsx", _
"Central Pacific Bank-automate.xlsx", "Charles Schwab-automate.xlsx", "Citibank-automate.xlsx", "Citizens-automate.xlsx", _
"Coastal Federal-automate.xlsx", "Colonial Bank-automate.xlsx", "Comerica-automate.xlsx", "Commerce-automate.xlsx", _
"Compass-automate.xlsx", "Delta Community CU-automate.xlsx", "Desert Schools-automate.xlsx", "Discover-automate.xlsx", _
"Eastern-automate.xlsx", "Edward Jones-automate.xlsx", "Fidelity Investments-automate.xlsx", "First Bank Data-automate.xlsx", _
"First Banks-automate.xlsx", "First Niagara-automate.xlsx", "First Technology-automate.xlsx", _
"Flagstar-automate.xlsx", "FNB of PA-automate.xlsx", "Fulton Financial-automate.xlsx", "Golden One-automate.xlsx", _
"Goldman Sachs-automate.xlsx", "Green Dot-automate.xlsx", "HSBC-automate.xlsx", "ING-automate.xlsx", "Investors Bank-automate.xlsx", _
"JP Morgan Chase-automate.xlsx", "M&T-automate.xlsx", "Mellon-automate.xlsx", "Merrill Lynch-automate.xlsx", "Mission-automate.xlsx", _
"Morgan Stanley-automate.xlsx", "Mutual of Omaha-automate.xlsx", "National Penn-automate.xlsx", "Navy-automate.xlsx", _
"New York Community-automate.xlsx", "Northern Trust-automate.xlsx", "Old National-automate.xlsx", "OnPoint-automate.xlsx", _
"Pershing-automate.xlsx", "PNC-automate.xlsx", "Rabobank-automate.xlsx", "Randolph Brooks-automate.xlsx", "Raymond James-automate.xlsx", _
"RBC Georgia-automate.xlsx", "Regions Bank-automate.xlsx", "San Diego County CU-automate.xlsx", "Sandia Labs-automate.xlsx", _
"Santander-automate.xlsx", "Simple Bank-automate.xlsx", "Space Coast-automate", "Stanford-automate.xlsx", "Suntrust-automate.xlsx", _
"Synovus-automate.xlsx", "TD Bank-automate.xlsx", "Technology CU-automate.xlsx", "Tri Counties Bank-automate.xlsx", _
"Umpqua-automate.xlsx", "Union Bank-automate.xlsx", "US Bank-automate.xlsx", "USAA-automate.xlsx", "Utah Community CU-automate.xlsx", _
"Wells Fargo-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
        Set thisWB = Workbooks.Open(filename:=sFileName)




'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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I recreated a new module and copied the code and that really did it.

However, I think I need to modify the following: Set thisWB = Workbooks.Open(filename:=sFileName) but I'm not sure how
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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