Exce Macros

Using Excel 2000 can you create a macro that works across more than one
workbook? For example, can a macro start running in one workbook and finish
in another? If so, is there any special code or actions you need to do to
make it work?

Thanks,
--
LPS

Yes...ish but we need a little more clarification. Do you want to

1. manipulate another workbook
2. run code that exists in another workbook

Bare in mind the the thread of execution will always end in the workbook
that began the process, but that workbook does not need to be the active
workbook when the code ends.
--
HTH...

Jim Thomlinson


"LPS" wrote:

> Using Excel 2000 can you create a macro that works across more than one
> workbook? For example, can a macro start running in one workbook and finish
> in another? If so, is there any special code or actions you need to do to
> make it work?
>
> Thanks,
> --
> LPS

Hi Jim. Thank you for such a fast response. I want to be able to manipulate
a second workbook from a macro I start in the first workbook. It doesn't
matter where the execution ends.
--
LPS


"Jim Thomlinson" wrote:

> Yes...ish but we need a little more clarification. Do you want to
>
> 1. manipulate another workbook
> 2. run code that exists in another workbook
>
> Bare in mind the the thread of execution will always end in the workbook
> that began the process, but that workbook does not need to be the active
> workbook when the code ends.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "LPS" wrote:
>
> > Using Excel 2000 can you create a macro that works across more than one
> > workbook? For example, can a macro start running in one workbook and finish
> > in another? If so, is there any special code or actions you need to do to
> > make it work?
> >
> > Thanks,
> > --
> > LPS

Here is a very basic example. It modifies a workbook called That.xls. It
first checks if the book is open. if not it opens it. There is a workbook
object called wbkTarget that gives you a reference to the workbook we are
manipulating. The code is running in "ThisWorkbook".

Sub ModifyOtherBook()
Dim wbkTarget As Workbook

On Error Resume Next
Set wbkTarget = Workbooks("That.xls")
On Error GoTo 0

If wbkTarget Is Nothing Then Workbooks.Open ("C:\That.xls")

wbkTarget.Sheets("Sheet1").Range("A1").Value = "Tada"
End Sub
--
HTH...

Jim Thomlinson


"LPS" wrote:

> Hi Jim. Thank you for such a fast response. I want to be able to manipulate
> a second workbook from a macro I start in the first workbook. It doesn't
> matter where the execution ends.
> --
> LPS
>
>
> "Jim Thomlinson" wrote:
>
> > Yes...ish but we need a little more clarification. Do you want to
> >
> > 1. manipulate another workbook
> > 2. run code that exists in another workbook
> >
> > Bare in mind the the thread of execution will always end in the workbook
> > that began the process, but that workbook does not need to be the active
> > workbook when the code ends.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "LPS" wrote:
> >
> > > Using Excel 2000 can you create a macro that works across more than one
> > > workbook? For example, can a macro start running in one workbook and finish
> > > in another? If so, is there any special code or actions you need to do to
> > > make it work?
> > >
> > > Thanks,
> > > --
> > > LPS

Thank you. I will experiment with this and I'm sure it will help.
--
LPS


"Jim Thomlinson" wrote:

> Here is a very basic example. It modifies a workbook called That.xls. It
> first checks if the book is open. if not it opens it. There is a workbook
> object called wbkTarget that gives you a reference to the workbook we are
> manipulating. The code is running in "ThisWorkbook".
>
> Sub ModifyOtherBook()
> Dim wbkTarget As Workbook
>
> On Error Resume Next
> Set wbkTarget = Workbooks("That.xls")
> On Error GoTo 0
>
> If wbkTarget Is Nothing Then Workbooks.Open ("C:\That.xls")
>
> wbkTarget.Sheets("Sheet1").Range("A1").Value = "Tada"
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "LPS" wrote:
>
> > Hi Jim. Thank you for such a fast response. I want to be able to manipulate
> > a second workbook from a macro I start in the first workbook. It doesn't
> > matter where the execution ends.
> > --
> > LPS
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Yes...ish but we need a little more clarification. Do you want to
> > >
> > > 1. manipulate another workbook
> > > 2. run code that exists in another workbook
> > >
> > > Bare in mind the the thread of execution will always end in the workbook
> > > that began the process, but that workbook does not need to be the active
> > > workbook when the code ends.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "LPS" wrote:
> > >
> > > > Using Excel 2000 can you create a macro that works across more than one
> > > > workbook? For example, can a macro start running in one workbook and finish
> > > > in another? If so, is there any special code or actions you need to do to
> > > > make it work?
> > > >
> > > > Thanks,
> > > > --
> > > > LPS

I just reread my response and it assumes taht you have a certian amount of
understanding. If you are a little lost in the technical details reply back
and I will give you some better pointers.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Here is a very basic example. It modifies a workbook called That.xls. It
> first checks if the book is open. if not it opens it. There is a workbook
> object called wbkTarget that gives you a reference to the workbook we are
> manipulating. The code is running in "ThisWorkbook".
>
> Sub ModifyOtherBook()
> Dim wbkTarget As Workbook
>
> On Error Resume Next
> Set wbkTarget = Workbooks("That.xls")
> On Error GoTo 0
>
> If wbkTarget Is Nothing Then Workbooks.Open ("C:\That.xls")
>
> wbkTarget.Sheets("Sheet1").Range("A1").Value = "Tada"
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "LPS" wrote:
>
> > Hi Jim. Thank you for such a fast response. I want to be able to manipulate
> > a second workbook from a macro I start in the first workbook. It doesn't
> > matter where the execution ends.
> > --
> > LPS
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Yes...ish but we need a little more clarification. Do you want to
> > >
> > > 1. manipulate another workbook
> > > 2. run code that exists in another workbook
> > >
> > > Bare in mind the the thread of execution will always end in the workbook
> > > that began the process, but that workbook does not need to be the active
> > > workbook when the code ends.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "LPS" wrote:
> > >
> > > > Using Excel 2000 can you create a macro that works across more than one
> > > > workbook? For example, can a macro start running in one workbook and finish
> > > > in another? If so, is there any special code or actions you need to do to
> > > > make it work?
> > > >
> > > > Thanks,
> > > > --
> > > > LPS

Sorry there is a small goof in my original code. Try this...

Sub ModifyOtherBook()
Dim wbkTarget As Workbook

On Error Resume Next
Set wbkTarget = Workbooks("That.xls")
On Error GoTo 0

If wbkTarget Is Nothing Then Set wbkTarget = Workbooks.Open("C:\That.xls")

wbkTarget.Sheets("Sheet1").Range("A1").Value = "Tada"
End Sub
--
HTH...

Jim Thomlinson


"LPS" wrote:

> Thank you. I will experiment with this and I'm sure it will help.
> --
> LPS
>
>
> "Jim Thomlinson" wrote:
>
> > Here is a very basic example. It modifies a workbook called That.xls. It
> > first checks if the book is open. if not it opens it. There is a workbook
> > object called wbkTarget that gives you a reference to the workbook we are
> > manipulating. The code is running in "ThisWorkbook".
> >
> > Sub ModifyOtherBook()
> > Dim wbkTarget As Workbook
> >
> > On Error Resume Next
> > Set wbkTarget = Workbooks("That.xls")
> > On Error GoTo 0
> >
> > If wbkTarget Is Nothing Then Workbooks.Open ("C:\That.xls")
> >
> > wbkTarget.Sheets("Sheet1").Range("A1").Value = "Tada"
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "LPS" wrote:
> >
> > > Hi Jim. Thank you for such a fast response. I want to be able to manipulate
> > > a second workbook from a macro I start in the first workbook. It doesn't
> > > matter where the execution ends.
> > > --
> > > LPS
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Yes...ish but we need a little more clarification. Do you want to
> > > >
> > > > 1. manipulate another workbook
> > > > 2. run code that exists in another workbook
> > > >
> > > > Bare in mind the the thread of execution will always end in the workbook
> > > > that began the process, but that workbook does not need to be the active
> > > > workbook when the code ends.
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "LPS" wrote:
> > > >
> > > > > Using Excel 2000 can you create a macro that works across more than one
> > > > > workbook? For example, can a macro start running in one workbook and finish
> > > > > in another? If so, is there any special code or actions you need to do to
> > > > > make it work?
> > > > >
> > > > > Thanks,
> > > > > --
> > > > > LPS

I must admit that I am a little confused by this. I think I understand the
general gist of things but I am not sure how to customize it to my workbooks.
I will play around with it next week and if I have problems will let you
know.

Many thanks for you help.
--
LPS


"Jim Thomlinson" wrote:

> I just reread my response and it assumes taht you have a certian amount of
> understanding. If you are a little lost in the technical details reply back
> and I will give you some better pointers.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Jim Thomlinson" wrote:
>
> > Here is a very basic example. It modifies a workbook called That.xls. It
> > first checks if the book is open. if not it opens it. There is a workbook
> > object called wbkTarget that gives you a reference to the workbook we are
> > manipulating. The code is running in "ThisWorkbook".
> >
> > Sub ModifyOtherBook()
> > Dim wbkTarget As Workbook
> >
> > On Error Resume Next
> > Set wbkTarget = Workbooks("That.xls")
> > On Error GoTo 0
> >
> > If wbkTarget Is Nothing Then Workbooks.Open ("C:\That.xls")
> >
> > wbkTarget.Sheets("Sheet1").Range("A1").Value = "Tada"
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "LPS" wrote:
> >
> > > Hi Jim. Thank you for such a fast response. I want to be able to manipulate
> > > a second workbook from a macro I start in the first workbook. It doesn't
> > > matter where the execution ends.
> > > --
> > > LPS
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Yes...ish but we need a little more clarification. Do you want to
> > > >
> > > > 1. manipulate another workbook
> > > > 2. run code that exists in another workbook
> > > >
> > > > Bare in mind the the thread of execution will always end in the workbook
> > > > that began the process, but that workbook does not need to be the active
> > > > workbook when the code ends.
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "LPS" wrote:
> > > >
> > > > > Using Excel 2000 can you create a macro that works across more than one
> > > > > workbook? For example, can a macro start running in one workbook and finish
> > > > > in another? If so, is there any special code or actions you need to do to
> > > > > make it work?
> > > > >
> > > > > Thanks,
> > > > > --
> > > > > LPS