Application.Workbooks("try.xls").Close problem

justinferns · October 3rd, 2011, 10:52 AM

Hi,

I'm trying to close an open excel using the code :

Application.Workbooks("try.xls").Close

This above line works in Excel 2007 but not in Excel 2003. It gives me this error: "Subscript out of range"

Can anyone give me a suitable code that would work in Excel 2003?

Thanks
Justin

mtranchi · October 3rd, 2011, 11:45 AM

No longer have Excel 2003, so can't try it, but it should work. Are you sure you have both the name and extension (i.e. it's not an .xlsx or .xlsm file) right? And that the workbook is in fact open?

justinferns · October 3rd, 2011, 11:51 AM

Yes, I've tried all possible ways (I guess), but it still gives me this Run time error 9 - Subscript out of range and I have no clue why.

Justin

mtranchi · October 3rd, 2011, 12:02 PM

That's a mystery. "Subscript out of range" in this case means that it is not finding a file called "try.xls", so your problem lies there.

Only two things i can think of to see if you can narrow down why it's not working, one, try

Code:

Sub TryThis()
Workbooks("try.xls").Close
'instead of
'Application.Workbooks("try.xls").Close
End Sub

or try closing all workbooks, then open the workbook with the macro first, then open the "try.xls" workbook next, then try

Code:

Sub TryThis()
Workbooks(2).Close
End Sub

see if that makes any diff

justinferns · October 3rd, 2011, 12:20 PM

Thanks a ton... I just created a new excel workbook and pasted the same code that was giving an error and to my luck it worked.

But the sad part is it's not working for my current application and i have no clue why.

Justin

mtranchi · October 3rd, 2011, 12:56 PM

Excel VBA is kind of buggy. If your application was originally developed as a 2007 version and then you saved it as a 2003 version, you may be f*cked and have to start over from scratch with it as a 2003 version, at least as far as i know. If that's the route you take, you can export your modules, then import them into the new 2003 workbook (though if you have code behind the spreadsheets, i'd copy and paste those, same thing with user forms or any other class modules not of your own making), or simply copy and paste the code. I hope it's not a massive project, if it is, hopefully someone will come along who knows how to fix that issue (I'd love to know myself) before you're forced to make a move.

Good luck.

justinferns · October 3rd, 2011, 01:51 PM

Thanks mate!! :)

Justin

mtranchi · October 3rd, 2011, 01:56 PM

no problem, glad to be of help :)

JP · October 14th, 2011, 01:34 PM

Quote:

Originally Posted by justinferns

Hi,

I'm trying to close an open excel using the code :

Application.Workbooks("try.xls").Close

This above line works in Excel 2007 but not in Excel 2003. It gives me this error: "Subscript out of range"

Can anyone give me a suitable code that would work in Excel 2003?

Thanks
Justin

Works for me in Excel 2003. There must be something else going on. Can you post your full code?