Unable to Set workbook variable

[davidam]

I am trying to set a Workbook variable with the following

Public sendBook as Workbook
Public bookName as String
Set sendBook = Workbooks(bookName & ".xls")
End Sub

I keep getting 'subscript out of range' errors on this
I know that the string variable is good because I have tested it by having the code paste it into a range. I have also tried pasting the string and then using the range value but nothing works. Can anyone tell me what I am doing wrong?
Thanks

 

[xenou]

the workbook needs to be open. What is the value of variable bookname?

 

[davidam]

In this instance it is a number derived from the Now() function. The workbook that creates the variable is indeed closed at this point in the code...but does not the fact that the variable is public eliminate the need to have the workbook open. Also, I have tried pasting the variable into the workbook that holds the code and then using that range value to set the w/b varialbe and it still does not work.

 

[xenou]

Sorry, you really can't set a reference to a workbook that is closed. Workbooks(x) refers to the workbooks collection, and only open workbooks are members of that collection.
ξ

 

[davidam]

Well I am still a bit confused...but that did seem to work.

 

[davidam]

Yes...OK, that makes sense now. Thanks!