Exce Macros

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

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

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