The code i wrote was working perfectly until i openned an irrelevant .xls file in another folder and saved it
From that moment, when i run the code it tries to open the file that i saved (the irrelevant one) from the folder i specify in the code and of course it cannot find it
I suspect that the code tries to open the most recent saved file
here is the code
Dim oWbk As Workbook
Dim sFil As Variant
Dim sPath As Variant
Dim i As Integer
i = 2
' The following path is the location of the folder where the spread files are saved
'sPath = Sheets("Spreads List").Cells(j, 16366).Value
sPath = "\\ciserver1\spreads\New Spreads\MENA\2012\"
ChDir sPath
sFil = Dir("*.xls") 'this is the type of files that we want to open that are saved in the above path
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through
' every time a file (i.e. workbook) is openned, it gives it the name oWbk
Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file
' The following is the full path of the file openned
myname = oWbk.path & "\" & oWbk.Name
' Copies the name of the issuer from oprnnrd workbook in sheets "bank" cells(1,1)
oWbk.Worksheets("RATIOS").Cells(1, 1).Copy
'Pastes the name in the "Spreads List" sheet
Windows(Credit_Score_Workbook).Activate
Sheets("Spreads List").Activate
Cells(i, 16360).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copies the file path in "Spreads List" sheet
Cells(i, 16361) = myname
i = i + 1
Application.DisplayAlerts = False
oWbk.Close
Application.DisplayAlerts = True
' oWbk.Close True 'close the workbook, saving changes
Loop ' End of LOOP
Bookmarks