I need to work between 3 different excel workbooks. I enter the
location path for 2 of the workbooks in a cell and define a name for
each of them.

I can then open the workbook if the filename changed simply by
changing in the cell that is defined.

Windows("BOOK1.xls").Activate
Sheets("sheet 1").Select
Loc1 = Range("path1")
Workbooks.Open Filename:=loc1

Windows("BOOK1.xls").Activate
Sheets("sheet 1").Select
Loc2 = Range("path2")
Workbooks.Open Filename:=loc2


How do i make use of the defined name to activate the workbook to work
on? the whole idea is something like this:

Windows("loc1").Activate

When you open the workbook, then that newly opened workbook should be the
activeworkbook. You shouldn't have to do more.

But I'd use something like:

Dim Loc1 as string
dim wkbk1 as workbook

loc1 = workbooks("book1.xls").range("Path1").value
set wkbk1 = nothing
on error resume next
set wkbk1 = workbooks.open(filename:=loc1)
on error goto 0

if wkbk1 is nothing then
msgbox "That file: " & loc1 & " doesn't exist"
else
wkbk1.activate
end if

======
And similar stuff for the other two workbooks/locations

Then I could refer to each workbook by wkbk1, wkbk2, wkbk3.

In fact, those variable names could be confusing. I'd use more mnemonically
significant names:

Dim PricesWkbk as workbook
dim StatusWkbk as workbook
dim EmployeesWkbk as workbook

(or what ever made sense to you.)

wel wrote:
>
> I need to work between 3 different excel workbooks. I enter the
> location path for 2 of the workbooks in a cell and define a name for
> each of them.
>
> I can then open the workbook if the filename changed simply by
> changing in the cell that is defined.
>
> Windows("BOOK1.xls").Activate
> Sheets("sheet 1").Select
> Loc1 = Range("path1")
> Workbooks.Open Filename:=loc1
>
> Windows("BOOK1.xls").Activate
> Sheets("sheet 1").Select
> Loc2 = Range("path2")
> Workbooks.Open Filename:=loc2
>
> How do i make use of the defined name to activate the workbook to work
> on? the whole idea is something like this:
>
> Windows("loc1").Activate

--

Dave Peterson

Many thanks Dave.

Reason I using this is because I need to perform a lot of cut and
paste among 3 workbooks, and their filename changed monthly as in

Rec_Aug07, Rec_Sep07, Rec_Oct07 and so on...

I will try out your method.

Tks.

On Sep 3, 8:19 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> When you open the workbook, then that newly opened workbook should be the
> activeworkbook. You shouldn't have to do more.
>
> But I'd use something like:
>
> Dim Loc1 as string
> dim wkbk1 as workbook
>
> loc1 = workbooks("book1.xls").range("Path1").value
> set wkbk1 = nothing
> on error resume next
> set wkbk1 = workbooks.open(filename:=loc1)
> on error goto 0
>
> if wkbk1 is nothing then
> msgbox "That file: " & loc1 & " doesn't exist"
> else
> wkbk1.activate
> end if
>
> ======
> And similar stuff for the other two workbooks/locations
>
> Then I could refer to each workbook by wkbk1, wkbk2, wkbk3.
>
> In fact, those variable names could be confusing. I'd use more mnemonically
> significant names:
>
> Dim PricesWkbk as workbook
> dim StatusWkbk as workbook
> dim EmployeesWkbk as workbook
>
> (or what ever made sense to you.)
>
>
>
>
>
> wel wrote:
>
> > I need to work between 3 different excel workbooks. I enter the
> > location path for 2 of the workbooks in a cell and define a name for
> > each of them.
>
> > I can then open the workbook if the filename changed simply by
> > changing in the cell that is defined.
>
> > Windows("BOOK1.xls").Activate
> > Sheets("sheet 1").Select
> > Loc1 = Range("path1")
> > Workbooks.Open Filename:=loc1
>
> > Windows("BOOK1.xls").Activate
> > Sheets("sheet 1").Select
> > Loc2 = Range("path2")
> > Workbooks.Open Filename:=loc2
>
> > How do i make use of the defined name to activate the workbook to work
> > on? the whole idea is something like this:
>
> > Windows("loc1").Activate
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -