Working with 2 workbooks

[Guest]

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.

 

[Tom Ogilvy]

if you use

Dim mySheet as Worksheet
set mySheet = Activesheet

then the test is

if not mysheet is nothing then

 

[Guest]

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

 

[Tom Ogilvy]

You must have favorite typos like me <g>

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

would work better.

 

[Guest]

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