PDA

View Full Version : Solved: Workbook open problem



jwise
10-28-2007, 07:03 PM
I found some code on a web site. When I tried it, I got a problem. I searched to find the resolution and found another web site with the identical statement:
Dim wbMine as Workbook
...
Set wbMine = Workbooks("C:\data\Test4.xls")
...


The error I'm getting is that "Workbooks" has a bad index. There is a workbook with that name on the indicated drive and directory. What gives?

mikerickson
10-28-2007, 07:09 PM
Workbooks("workbookName") expects that a workbook named "workbookName" has been opened by the same instance of Excel that is running the code.
(I think that "There is a file with that name on the indicated drive and directory." is the prefered MicroSoft terminology.)

Each workbook refered to by WorkBook("name") needs to be open.

xld
10-29-2007, 01:51 AM
Dim wbMine As Workbook
On Error Resum Next
Set wbMine = Workbooks("Test4.xls")
On Error Goto 0
If wbMine Is Nothing Then
Set wbMine = Workbooks.Open("C:\data\Test4.xls")
End If

jwise
10-29-2007, 07:18 AM
Thanks for the information. It does make sense, but it seems strange to me that it works this way. I have no idea why you would want VBA to access an already-open workbook. Wouldn't you already have access to the data?

The supplied code is interesting... a way around VBA's behavior, or on a more positive note, a solution within the given framework.

Thanks again.

rory
10-29-2007, 07:36 AM
The main purpose of VBA is to automate tasks. Obviously it will be extremely useful for VBA to be able to access and manipulate data in open workbooks for this! Otherwise, what would be the need for VBA?

jwise
10-29-2007, 08:04 AM
Thanks for the insight, Rory.

I think I did a poor job of stating my complaint. It just seemed odd that an already accessed spreadsheet would need to be accessed again from VBA code in a different workbook. I think maybe I didn't consider things like "add-ins" where the code you need is in the add-in and not in the workbook which the add-in processes. Otherwise, you could only process data in the same workbook as the code. Also, since you can have multiple workbooks open, and you might want to alter the data in a particular workbook from this add-in, this is the way you tell VBA which of the open workbooks to choose. Am I close? I guess my complaint is not very well founded!

rory
10-29-2007, 08:11 AM
Correct. As a general rule, if you don't specify which workbook/worksheet to act on, code will run against the active sheet in the active workbook.

jwise
11-08-2007, 10:27 AM
Thanks again for the help.

Zack Barresse
11-08-2007, 11:14 AM
As a general rule...
General? How about all the darn time.. LOL! :p

rory
11-08-2007, 04:18 PM
Not necessarily in a worksheet module!

Zack Barresse
11-09-2007, 09:53 AM
ROFL! Well if that's not the most literal assumption.... ;)

XLGibbs
11-09-2007, 04:12 PM
As is often the case:

"I hate that my code does what I TELL IT TO DO instead of WHAT I WANT IT TO DO"

how is excel supposed to know what you "mean" if you don't tell it..

xld
11-09-2007, 04:21 PM
Why not? MS seems to insist that thye know what I want to do and how I want to do it, so why shouldn't Excel know what I want to do?

Zack Barresse
11-09-2007, 04:24 PM
Well while they're at it they can make me dinner too.

xld
11-09-2007, 04:26 PM
When you can stop looking in the mirror!

Zack Barresse
11-09-2007, 04:27 PM
At what, that devilishly handsome fellow? I see no reason...