avoiding error 1004 method open of object workbooks failed

[khawarameer]

I am trying to merge some excel 2003 work books in one excel 2010 workbook with VBA and experiencing following error
"Error 1004 method open of object workbooks failed"

Following code is used.

Sub GetSheets()
Path = "C:\Users\bal-admin\Desktop\Transfer Register\"
Dim number As Integer
number = 1
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
ActiveSheet.Name = number
Sheet.Copy After:=ThisWorkbook.Sheets(1)
number = number + 1
Next Sheet
Workbooks(Filename).Close savechanges:=False
Filename = Dir()
Loop
End Sub

Any solution please

 

[Andrew Poulsom]

Are you sure that the file you are trying to open is a valid Excel file? It must exist because Dir has returned its name. Can you open it manually?

 

[par60056]

is it possible that there is a macro run when the book is opened? Are you sure it is the open giving the error and not the close?

Looking at your code I see a couple things that are going to cause problems.

 

[khawarameer]

Are you sure that the file you are trying to open is a valid Excel file? It must exist because Dir has returned its name. Can you open it manually?

file error: data may have been lost."however file are open when press ok

 

[par60056]

try looking at the "CorruptLoad" parameter. maybe CorruptLoad:=xlRepairFile will let it open.

 

[khawarameer]

try looking at the "CorruptLoad" parameter. maybe CorruptLoad:=xlRepairFile will let it open.

Thank you sir, the parameter worked fine.

Regards.