PDA

View Full Version : setting a workbook as a constant



choubix
06-10-2008, 10:02 PM
hello,

I have a workbook that is going to dig up data from other workbooks.

is it possible to set these workbooks (data source) as constants so I don't have to change every single line where their name appears in case we need to change their name??

I tried somehting like this:

Public Const input = "myworkbook1.xls"

and then tried to call:

workbooks("input").activate

it didnt work.

my idea is to set all the workbooks I need to use as constant and to call them like this:

input.activate
input1.activate etcetc

any idea please??

mdmackillop
06-10-2008, 11:52 PM
Sub Test()
Dim WB1 As Workbook, WB2 As Workbook 'etc
Set WB1 = Workbooks.Open("C:\AAA\Book2.xls")
Set WB21 = Workbooks.Open("C:\AAA\Book3.xls")
WB1.Activate
End Sub

Bob Phillips
06-10-2008, 11:54 PM
That should work, thae activate line that is but better like this



Public wb1 As Workbook
Public wb2 As Workbook

Sub MySub()

Set wb1 = Workbooks("myworkbook1.xls")
Set wb2 = Workbooks("myworkbook2.xls")

'...

wb1.Activate

'...

wb2.Activate

mdmackillop
06-10-2008, 11:58 PM
Hi Choubix,
When you use a variable, do not enclose it in quotes, or it is read as a string

workbooks("input").activate

should be

workbooks(input).activate