Solved: Workbook open problem

[jwise]

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:

[vba] Dim wbMine as Workbook
...
Set wbMine = Workbooks("C:\data\Test4.xls")
...
[/vba]

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]

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.

[Bob Phillips]

[vba]
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
[/vba]

[jwise]

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]

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]

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]

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]

Thanks again for the help.

[Zack Barresse]

As a general rule...

General? How about all the darn time.. LOL!

[rory]

Not necessarily in a worksheet module!

[Zack Barresse]

ROFL! Well if that's not the most literal assumption....

[XLGibbs]

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..

[Bob Phillips]

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]

Well while they're at it they can make me dinner too.

[Bob Phillips]

When you can stop looking in the mirror!

[Zack Barresse]

At what, that devilishly handsome fellow? I see no reason...