Urgent Excel 2010 Macro error “1004 Method Open of Workbook Failed” and Run Time Error 9

Hi Friends,
I am new to this forum and I really look forward to get help from experts to fix this issue for me.
Couple of our users in office is getting the error mentioned above while running the Macro in an excel which is used to generate P.O.. In my research most of the forums were pointing out to possible file corruption. I tried repairing the excel file in question "PO_Summary" and tried copying the content to a new file and replacing the old one with the new one and so one. I also tried passing the argument CorruptLoad: xlrepairFile option also. But now I am getting error Run Time Error 9 Script Out of Range. Can some one please help me as this is very urgent and I am getting now where.

Here is the full script. Here you can see the filed where it opens the worksheet "PO_Smmary.xlsx" (the file giving me trouble). I am unable to attach the excel with macro as its not enabled to upload. Kindly review and let me know the options. I just have a doubt in mind, that can this error be due to the file "PO_Summary" be open and in use by some user and when Macro runs its unable to open the file. If yes, can some one tell me a way to work this issue out.

Please Login or Register  to view this content.