Activate Other Workbook

[pauluk]

Hi,

How do you activate another workbook if you do not know the name of it
I know this sounds silly but i am being sent a workbook each week an
am unsure if the name is going to change. Any help would be great.

Also can you set a macro to copy itself from one workbook to another

 

[Rob van Gelder]

"Sent a workbook" - does that mean it's e-mailed to you? Do you want to
process the e-mail for the attachment and deal with it there?

Otherwise, simply save the attachment with a consistent name.

 

[Bob Phillips]

If you use code to access it when opened, you get a handle to it.

Set newWB = ActiveWorkbook

then you can refer to myWB thereafter, active or not.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pauluk]

Bob this is what it will be

Theuser will be sent the file via e-mail they then open this file an
also anothere file called data split

I need the data split file to recongize the sent file. It will b
called something different each time it is sent because will hav
differnet info but this will be held the same.

All i need to know is when the sent file is open how do i get my cod
from the split data to reconignze the open file if i have not specifie
a name

 

[Bob Phillips]

Paul,

Does the user know the difference between the two files. What I am thinking
is that if you can get them tom open data first, that could have code to
trap any other opens and ask the user if this is the one?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pauluk]

The user will know the differnece.

I was thinking of placeing an input box at the start for the user t
input the file name. But then it really does depend on the user placin
the correct name otherwise the coding will not work

 

[Bob Phillips]

No we can do better than that.

What we do is add application events to trap every workbook being opened. On
open, we ask if this is it, if so we get workbook automatically. This
workbook object will be stored in thisWb, so just use thisWb when
referencing the data thereafter.

Firstly, all of this code goes in the designated workbook.

'========================================
In a standard code module, declare a public variable of thisWB

Public thisWB as Workbook

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim ans

ans = InputBox("Is the file?", vbYesNo)
If ans = vbYes Then
thisWB = ActiveWorkbook
End If

End Sub
'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pauluk]

Bob thanks for this.

There is just a samll problem at the moment. with the following

Dim App As New clsAppEvents

its coming up with user-defined type not defined.

This what is in the module:
Public thisWB As Workbook
Dim App As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub
Sub testr()
Windows("thisWB").Activate

End Sub

This is what is in the class
Option Explicit
Public WithEvents AppClass As Application
Private Sub App_WorkbookOpen(ByVal WB As Workbook)

Dim ans

ans = InputBox("Is this the file you wish split amongst the team?", ""
vbYesNo)
If ans = vbYes Then
thisWB = ActiveWorkbook
End If
End Su

 

[Bob Phillips]

Paul,

As I metioned, you must rename the class module to clsAppEvents, otherwise
you get that error.

That or change the code to
Dim App As New class1
if ytou want to be sloppy<G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pauluk]

Thanks Bob this is ok now.

so do i just use thisWB to refernce in any code i am writting?

i.e Windows(thisWB).Activate

would i need to add anything else?

Sorry to bug you like this but it is the only real way that i am goin
to learn. Beacuse of the questions i have been asking over the past fe
months i am going to list all the refeneces on my site for othe
newbies to us

 

[Bob Phillips]

You don't even need Windows as thisWb is a workbook object not a string. So
you just use

thisWB.Activate

this then makes that workbook the active workbook, so you can then use the
activeworkbook object, like so

ActiveWorkbook.Worksheets(1)
etc.

Or you could just use the workbook object directly

thisWB.Worksheets(1)
etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pauluk]

Bob,

i am still having problems i have made the changes. but when i place
simple code.

Sub testr()

thisWB.Activate

End Sub

I just get object undefined. I am unsure of the class module as persum
this is what defines thisWB

I thought that the inputbox would popup once i had opened anothe
workbook. but no jo

 

[Chip Pearson]

The 'thisWB' variable must be declared somewhere and set to a
specific workbook. Perhaps you are thinking of the built-in
object ThisWorkbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[pauluk]

Ok i have changed thecodeing to the following

sub testr()
set thisWB = ThisWorkbook

thisWB.Activate
End Sub

As yet i have not been asked what i would like thisWB to refer to i d
not get the error message any more.

So i now need to know how the code in the class module is activated a
per above.

I think am going krazy, but just don't want to give up at all!

 

[pauluk]

ok got it work at last!!!! wo

 

[Bob Phillips]

thisWB is (should be!) a global variable that gets set by application event
code when a workbook is opened. Not ThisWorkbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

The 'thisWB' variable must be declared somewhere and set to a
specific workbook. Perhaps you are thinking of the built-in
object ThisWorkbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

Not by changing it to

set thisWB = ThisWorkbook

as that only works on the workbook that the code is in, whereas that
variable will point at the next opened workbook.

Are you sure?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)