Problem with references/variables

[Mats Samson]

Hello,
I’m creating references/variables to other worksheets in other workbooks
but they don’t work when returning to the original workbook sheet.
“Run time error 91: Object variable or With block variable not setâ€
The two workbooks are loaded at one time and in the first/â€mainâ€
(WTNSystem.xls)
there is a module containing:
Public WSS As Worksheet, WSC As Worksheet, WSD As Worksheet, WSO As Worksheet
Public Sub Autpen()
ChDir ("D:\My Documents\Excel\Calc")
Workbooks.Open Filename:="WTNDatabase.xls"
Set WSS = Workbooks("WTNSystem").Worksheets("System")
Set WSC = Workbooks("WTNSystem").Worksheets("Calculation")
Set WSD = Workbooks("WTNDatabase").Worksheets("Database")
Set WSO = Workbooks("WTNDatabase").Worksheets("Offers")
WSS.Activate
End Sub

The second workbook is opened and everything works fine as long as I stay
within the “main†workbook and I can switch to the WTNDatabase.xls workbook
but returning to WTNsystem.xls with the code: WSC.Activate triggers the error.

I even put the same code as above in the WTNDatabase.xls , but it doesn’t
help!!!
I cannot switch back to WTNSystem.xls with this “abbreviationâ€.

Why?

 

[Jim Thomlinson]

Before you can activate a worksheet you have to activate the workbook...
Give this a try...

Public WSS As Worksheet, WSC As Worksheet, WSD As Worksheet, WSO As Worksheet
Public WBS As Workbook, WBD As Workbook

Public Sub Autpen()
ChDir ("D:\My Documents\Excel\Calc")
Workbooks.Open Filename:="WTNDatabase.xls"

Set WBS = Workbooks("WTNSystem")
Set WBD = Workbooks("WTNDatabase")

Set WSS = WBS.Worksheets("System")
Set WSC = WBS.Worksheets("Calculation")
Set WSD = WBD.Worksheets("Database")
Set WSO = WBD.Worksheets("Offers")

WBS.Activate 'Activate Book
WSS.Activate
MsgBox "We are Here"
WBD.Activate 'Activate Book
WSD.Activate
MsgBox "And now we are here..."
End Sub

 

[GB]

Two things I can think of. One is a question:
What do you mean about switching to the other workbook?

As for coming back from the other workbook, well, I can only think that if
code is running in the other workbook, and it is trying to go "back" it
doesn't know where to go back to. There is no "transfer" of data in your
example. This is based on the scope of the data. The variable WSC or
whichever is defined and assigned only in the "main" datasheet, I think
that's what you called it. However, the value of WSC is not transferred to
the other datasheet, even if it has some form of a global variable. So after
opening it, if you called a public function of the other datasheet, you could
assign WSC to the same value as your "main" sheet.

 

[Mats Samson]

Thanks Jim but No it doesn’t work!
When I try to return FROM WTNDatabase by running the code:
Public Sub CommandButton1_Click()
ActiveWindow.WindowState = xlNormal
WBS.Activate
WSC.Activate
End Sub
I still get Error 91. I even tried to move this code from the Sheet to a
Module, but the problem remains. See also my reply to GB below.
BR
Mats

 

[Mats Samson]

Yes GB, I did!
I wrote in the last paragraph: “I even put the same code as above in the
WTNDatabase.xls , but it doesn’t help!!!â€

I tried to copy the code after trying Jim’s proposal as well. The same
Declaration and Autpen is run in both workbooks when they are opened
(Except the part opening WTNDatabase only in WTNSystem of course).
I really seem that PUBLIC variables are wasted when you switch between
Workbooks. Not at all as it is written in the VB Help file about Public
Statement (There is no Option Private Module in my code).

I don’t get it!?!?!
BR
Mats

 

[GB]

I did see that you said you put the same "code" in both files. But the
existence of the code does not mean that the object referenced in one file,
is the object referenced in the other.

For example, if I had a pile of parts, and enough parts to build at least
two full vehicles, and I said to one mechanic, go build me a car following
these instructions. And then to a second mechanic I gave him the same set of
instructions, and told him to go build me a car, the product should be two
cars. If they have followed the instructions to the T as I wrote them,
neither helped the other mechanic, and they produced two separate cars from
the same instructions and same pile of parts.

Now however, if in the instructions I gave every other instruction to each
mechanic, they would have to talk to one another and pass information.
Perhaps they may even help each other, and accomplish the same task obviously
in a shorter amount of time.

Now, how this relates to this situation. Your pile of parts is everything
on your system, HD, memory, etc. Your instructions, well you know... But
when one program says, go open a file, and that file says to open the other
file, in truth you are building a pile of files, not actually referencing
each other.

So what you would need to do, is open the first file. Have it open the
second file, then set some data value by use of either a public sub routine
or public function to equal a reference back to the original file. Like,
public sub SetWSCWorksheet(ReturnWorksheet as worksheet)
set WSC = ReturnWorksheet
end sub

And from the first workbook

Dim OtherWorkbookVariable as Workbook

Call OtherworkbookVariable.SetWSCWorksheet(WSC)

And create a similar function for each of the 4 variables that you want to
keep.

Also, the question becomes do you need to actually open the file everytime
it goes to auto_open, or if it is already open do you need to just make sure
that it is active? It seems that you could end up opening and opening and
opening, etc. the other file.

I'm not sure if I'm helping, but I am trying. I don't fully understand: 1)
what you are trying to do, 2) what you have done, 3) what has actually been
happening. But from what I do understand these have been my answers.

V/R,
GB

 

[Mats Samson]

Hi GB,
I was simply trying to create abbreviations for the file reference, like we
do when assigning a logical drive letter to an awesomely long filepath.
P: is much quicker and easier to write than
C:\My Documents\My Pictures\Camera\2005\Summer…

So instead of writing
Workbooks("WTNSystem").Worksheets("System").Activate, I could write
WBS.Activate
WSS.Activate.
Nice and tidy!
(It would have been even better if Excel had accepted WBS.WSS.Activate,
but it doesn’t!)

In my actual case I have a Business Calculation system and I want to save
the old data in a separate file so I can recall an old calculation next time
I need it.
So I load the WTNSystem.xls (the calculation) and in it’s auto_open it loads
WTNDatabase.xls (the saved data).
There are several reasons for keeping them in two files but 2 obvious are of
course that the “Database†will grow rather big by time and I can easily
share it with my colleague so they can use the information for their tasks.

The problem is that Excel doesn’t retain the variables between workbooks,
spite what is written in the help file. I found a workaround, that is to
place all such code where you are jumping between the workbooks in the
WTNSystem file, where the variables was initiated and use f.i. Run
(WTNSystem.xls!ReturnToCalc) from WTNDatabase.
But it seems awkward and you loose the logic that code should reside where
it is initiated.
I also discovered that the variables are not wasted, they still exist, but
only within the project (the workbook) they were created. So I can use the
abbreviations for all code within and from the WTNSystem workbook, but not
FROM other workbooks. And it doesn’t help to create the same variables in the
other workbooks, they don’t seem
to exist there. Strange!

It looks like below in WTNSystem:
Public WBS As Workbook, WBD As Workbook
Public WSS As Worksheet, WSC As Worksheet, WSD As Worksheet, WSO As Worksheet
Public Sub Autpen()
ChDir ("D:\My Documents\Excel\Calc")
Workbooks.Open Filename:="WTNDatabase.xls"
Set WBS = Workbooks("WTNSystem")
Set WBD = Workbooks("WTNDatabase")
Set WSS = Worksheets("System")
Set WSC = Worksheets("Calculation")
Set WSD = WBD.Worksheets("Database")
Set WSO = WBD.Worksheets("Offers")
WBS.Activate
WSS.Activate
End Sub