Working with 2 workbooks
 

I am currently writing code that opens a second workbook to pull infomration
from it. One of my subroutines is used by multiple buttons and sometimes the
subroutine needs to work on the sheet with the button, and sometimes it needs
to work on the opened workbook.

I have a public variable (MySheet) declared so I can set the WB i want, but
sometimes that variable is already filled when this subroutine activates, and
sometimes it needs to be filled.

I've tried things like if isempty(mysheet) or if mysheet = nothing and can't
seem to find a way to determine if mysheet is set or not, without the code
erroring. Anysuggestions on how to check to see if the variable is set or
not? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Working with 2 workbooks
 

if you use

Dim mySheet as Worksheet
set mySheet = Activesheet

then the test is

if not mysheet is nothing then


--
Regards,
Tom Ogilvy

"J Streger" wrote in message
...
I am currently writing code that opens a second workbook to pull
infomration
from it. One of my subroutines is used by multiple buttons and sometimes
the
subroutine needs to work on the sheet with the button, and sometimes it
needs
to work on the opened workbook.

I have a public variable (MySheet) declared so I can set the WB i want,
but
sometimes that variable is already filled when this subroutine activates,
and
sometimes it needs to be filled.

I've tried things like if isempty(mysheet) or if mysheet = nothing and
can't
seem to find a way to determine if mysheet is set or not, without the code
erroring. Anysuggestions on how to check to see if the variable is set or
not? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Working with 2 workbooks
 

When I am working with multiple books I always use workbook and worksheet
objects to keep things straight, something like this...

dim wbkTarget as workbook
dim wksTarget as worksheet

on error resume next
set wbkTarget = Workbooks.Open Filename:="C:\Test.xls"

if wbkTarget is nothing then
msgbox "Could not open file"
else
set wksTarget = wbkTarget.sheets("Sheet1")
if wksTarget is nothing then
msgbox "Could not locate sheet"
else
'Here is your sheet to play with...
endif
endif
on error goto 0

This is just a short example but I think it illustrates how to work in
mutiple sheets and multiple books...
--
HTH...

Jim Thomlinson


"J Streger" wrote:

I am currently writing code that opens a second workbook to pull infomration
from it. One of my subroutines is used by multiple buttons and sometimes the
subroutine needs to work on the sheet with the button, and sometimes it needs
to work on the opened workbook.

I have a public variable (MySheet) declared so I can set the WB i want, but
sometimes that variable is already filled when this subroutine activates, and
sometimes it needs to be filled.

I've tried things like if isempty(mysheet) or if mysheet = nothing and can't
seem to find a way to determine if mysheet is set or not, without the code
erroring. Anysuggestions on how to check to see if the variable is set or
not? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Working with 2 workbooks
 

You must have favorite typos like me <g

set wbkTarget = Workbooks.Open(Filename:="C:\Test.xls")

would work better.

--
Regards,
Tom Ogilvy




"Jim Thomlinson" wrote in message
...
When I am working with multiple books I always use workbook and worksheet
objects to keep things straight, something like this...

dim wbkTarget as workbook
dim wksTarget as worksheet

on error resume next
set wbkTarget = Workbooks.Open Filename:="C:\Test.xls"

if wbkTarget is nothing then
msgbox "Could not open file"
else
set wksTarget = wbkTarget.sheets("Sheet1")
if wksTarget is nothing then
msgbox "Could not locate sheet"
else
'Here is your sheet to play with...
endif
endif
on error goto 0

This is just a short example but I think it illustrates how to work in
mutiple sheets and multiple books...
--
HTH...

Jim Thomlinson


"J Streger" wrote:

I am currently writing code that opens a second workbook to pull
infomration
from it. One of my subroutines is used by multiple buttons and sometimes
the
subroutine needs to work on the sheet with the button, and sometimes it
needs
to work on the opened workbook.

I have a public variable (MySheet) declared so I can set the WB i want,
but
sometimes that variable is already filled when this subroutine
activates, and
sometimes it needs to be filled.

I've tried things like if isempty(mysheet) or if mysheet = nothing and
can't
seem to find a way to determine if mysheet is set or not, without the
code
erroring. Anysuggestions on how to check to see if the variable is set
or
not? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Working with 2 workbooks
 

Life is just one big typo with me... And spelling is not by forte either...
Bad spellers of the world untie!
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

You must have favorite typos like me <g

set wbkTarget = Workbooks.Open(Filename:="C:\Test.xls")

would work better.

--
Regards,
Tom Ogilvy




"Jim Thomlinson" wrote in message
...
When I am working with multiple books I always use workbook and worksheet
objects to keep things straight, something like this...

dim wbkTarget as workbook
dim wksTarget as worksheet

on error resume next
set wbkTarget = Workbooks.Open Filename:="C:\Test.xls"

if wbkTarget is nothing then
msgbox "Could not open file"
else
set wksTarget = wbkTarget.sheets("Sheet1")
if wksTarget is nothing then
msgbox "Could not locate sheet"
else
'Here is your sheet to play with...
endif
endif
on error goto 0

This is just a short example but I think it illustrates how to work in
mutiple sheets and multiple books...
--
HTH...

Jim Thomlinson


"J Streger" wrote:

I am currently writing code that opens a second workbook to pull
infomration
from it. One of my subroutines is used by multiple buttons and sometimes
the
subroutine needs to work on the sheet with the button, and sometimes it
needs
to work on the opened workbook.

I have a public variable (MySheet) declared so I can set the WB i want,
but
sometimes that variable is already filled when this subroutine
activates, and
sometimes it needs to be filled.

I've tried things like if isempty(mysheet) or if mysheet = nothing and
can't
seem to find a way to determine if mysheet is set or not, without the
code
erroring. Anysuggestions on how to check to see if the variable is set
or
not? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003