Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with the forms in different workbooks... | Excel Worksheet Functions | |||
Working with Different workbooks | Excel Discussion (Misc queries) | |||
Working with Different workbooks | Excel Discussion (Misc queries) | |||
Working with two workbooks | Excel Programming | |||
Working with Two WorkBooks | Excel Programming |