Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 3rd, 2011, 10:52 AM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Application.Workbooks("try.xls").Close problem

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
 
Old October 3rd, 2011, 11:45 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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?
 
Old October 3rd, 2011, 11:51 AM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 3rd, 2011, 12:02 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 3rd, 2011, 12:20 PM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 3rd, 2011, 12:56 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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.
 
Old October 3rd, 2011, 01:51 PM
Authorized User
 
Join Date: Aug 2011
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks mate!! :)

Justin
 
Old October 3rd, 2011, 01:56 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

no problem, glad to be of help :)
 
Old October 14th, 2011, 01:34 PM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Quote:
Originally Posted by justinferns View Post
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?
__________________
Regards,
JP
JP SoftTech





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 2:Dialog project "force close" error ichthus BOOK: Beginning Android Application Development 4 May 16th, 2011 11:16 AM
Base Page Problem with "safeitenames" carryover in the 'inherits" johnandrewmcknight BOOK: Beginning ASP.NET 4 : in C# and VB 3 April 22nd, 2011 04:08 PM
MSXSL gives error message for "for" inside "select" ilyaz XSLT 1 December 9th, 2010 05:02 PM
Add a CheckBox DataColumn to my DataGridView, Null format: "" or "True" but Error: F ismailc C# 2005 0 September 25th, 2009 04:56 AM
Earthquake Example problem "non-application token" Langley BOOK: Professional Android Application Development ISBN: 978-0-470-34471-2 2 January 28th, 2009 02:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.