I have this Excel spreadsheet in which I want to automate, I have two
macros once called Macro_GetData, and Macro_FormatData. Both these
Macros work fine, but when I try to automate them it runs the 2nd
Macro before the 1st Macro is even finish. I tried to use the
"Application.Wait" command but that just stopped the macros entirely.
What I want the macro to do is to run the first Macro (Macro_GetData)
and when that's finished then run the second Macro (Macro_FormatData).
The second Macro cannot work unless the first macro is completed. How
can I go about doing this?

Bobbak,
It sounds like the first macro "calls" the second macro before it should.
Try this: look at the code in Macro_GetData and move the line with
"Macro_FormatData" in it to the end of Macro_GetData.

If that screws it up or you can't find the applicable line then you had
better post the code.

Regards,
Jim Cone
San Francisco, CA

----- Original Message ----- ----------
From: "Bobbak"
Newsgroups: microsoft.public.excel.programming,microsoft.publi c.excel.misc
Sent: Tuesday, December 02, 2003 12:04 PM
Subject: HELP: Running two Macros, one before the other


I have this Excel spreadsheet in which I want to automate, I have two
macros once called Macro_GetData, and Macro_FormatData. Both these
Macros work fine, but when I try to automate them it runs the 2nd
Macro before the 1st Macro is even finish. I tried to use the
"Application.Wait" command but that just stopped the macros entirely.
What I want the macro to do is to run the first Macro (Macro_GetData)
and when that's finished then run the second Macro (Macro_FormatData).
The second Macro cannot work unless the first macro is completed. How
can I go about doing this?

Jim, what you suggested didn't work. I need the second macro to run
once the first macro is completed. In other words when the first marco
runs, once it's gets the data from the Import External Data (Web
Query), and fills out the worksheet then and only then have the macro
run the second macro. Here is the initial code that I have:


Sub Auto_Open()
Application.Run "'AgentStats (Auto).xls'!Macro_GetData" 'Calls the
Web Query
Application.Run "'AgentStats (Auto).xls'!Macro_FormatData" 'Formats
the Data
End Sub


Now the Macros Macro_GetData, and Macro_FormatData work perfectly when
I run them manually. What I need to figure out it to how to make work
that when the data from the Macro_GetData is loaded on the worksheet
to then run the Macro_FormatData.


"Jim Cone" wrote in message ...
Bobbak,
It sounds like the first macro "calls" the second macro before it should.
Try this: look at the code in Macro_GetData and move the line with
"Macro_FormatData" in it to the end of Macro_GetData.

If that screws it up or you can't find the applicable line then you had
better post the code.

Regards,
Jim Cone
San Francisco, CA

----- Original Message ----- ----------
From: "Bobbak"
Newsgroups: microsoft.public.excel.programming,microsoft.publi c.excel.misc
Sent: Tuesday, December 02, 2003 12:04 PM
Subject: HELP: Running two Macros, one before the other


I have this Excel spreadsheet in which I want to automate, I have two
macros once called Macro_GetData, and Macro_FormatData. Both these
Macros work fine, but when I try to automate them it runs the 2nd
Macro before the 1st Macro is even finish. I tried to use the
"Application.Wait" command but that just stopped the macros entirely.
What I want the macro to do is to run the first Macro (Macro_GetData)
and when that's finished then run the second Macro (Macro_FormatData).
The second Macro cannot work unless the first macro is completed. How
can I go about doing this?

Bobbak,

You got me.
It would seem, however, that you should be able to have the query set a
Boolean flag when the query completes and only then run your second macro.
Somebody else may be able to contribute here.

A possible crutch would be to check the worksheet for some entry that
signals the query is complete and then run the second macro.

Regards,
Jim Cone
San Francisco, CA

"Bobbak" wrote in message
om...
Jim, what you suggested didn't work. I need the second macro to run
once the first macro is completed. In other words when the first marco
runs, once it's gets the data from the Import External Data (Web
Query), and fills out the worksheet then and only then have the macro
run the second macro. Here is the initial code that I have:
Sub Auto_Open()
Application.Run "'AgentStats (Auto).xls'!Macro_GetData" 'Calls the
Web Query
Application.Run "'AgentStats (Auto).xls'!Macro_FormatData" 'Formats
the Data
End Sub
Now the Macros Macro_GetData, and Macro_FormatData work perfectly when
I run them manually. What I need to figure out it to how to make work
that when the data from the Macro_GetData is loaded on the worksheet
to then run the Macro_FormatData.

"Jim Cone" wrote in message
...
Bobbak,
It sounds like the first macro "calls" the second macro before it
should.
Try this: look at the code in Macro_GetData and move the line with
"Macro_FormatData" in it to the end of Macro_GetData.
If that screws it up or you can't find the applicable line then you had
better post the code.
Regards,
Jim Cone
San Francisco, CA
-snip-

Hi, I use the very simple way: the last line in the first macro simply
starts the second macro, by the line:

Application.Run "'ATCE020_ny.xls'!usafil"

ATCE020_ny.xls is the file where the second macro usafil is stored.

It works fine for me!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements