Calling a Macro from another workbook

[JohnJack]

Hi,

Yes I have read up on the different ways of doing this, but I always
come to the same problem.
In my current code I have the line

Application.Run ("T1Seed1.xls!Main")

Which works fine (it calls the "Main" macro in T1Seed2.xls). But once
the "main" macro is run, the rest of my current macro doesn't run.

Here is my code (missing some variables thata bit above, but thats
ok).

Dim ESDUFile, RootPath, WorkBookToOpen, SheetToOpen, BuildingHeight As
String

ESDUFile = Sheets("Rotate spf red").Cells(10, 9)
RootPath = ESDUFile

Do While Right(RootPath, 1) <> "\"
RootPath = Left(RootPath, Len(RootPath) - 1)
Loop

BuildingHeight = 225
SeedScale = 345
For A = 1 To NumTowers
For B = 1 To 2
SheetToOpen = "T" & A & "Seed" & B & ".xls"
WorkBookToOpen = RootPath & SheetToOpen

FileCopy ESDUFile, WorkBookToOpen

Range("C22:C57").Copy
Workbooks.Open WorkBookToOpen
Windows(SheetToOpen).Activate
Sheets("RWDI_Factors").Select

Range("H67").Select
ActiveSheet.Paste
Range("D7") = SeedScale
Range("D6") = BuildingHeight
Application.Run ("T1Seed1.xls!Main")

Sheets("Factors").Select

Application.CutCopyMode = False 'closes the clipboard with massive
amounts of data thus does not prompt to save or close
ActiveWorkbook.Close True ' false means not to save, true means to
save and close
Windows("MR_Setup_underDevelopment_profiles.xls").Activate

Next B
Next A

It works fine up until right after the Application.Run line where it
will not continue on (ie it will not select the "Factors" sheet or do
anything after).

Is there another way I can do this that will allow the original macro
to continue running after it calls the macro in the other workbook?

Any help would be appreciated.

Jack

 

[Arnie]

Can you show the code for the "main" macro?
Also, does this code stop during the first pass or on second or third loop?

 

[JohnJack]

No, actually I can't show the "main" macro. It's within a program
that is written by another company and has a password on it that I
can't get access to. I'm just trying to stream line a process where
the user has to open up multiple versions of this workbook and call
the "main" macro. This would allow the user to let their computer to
run for about 10 mins rather than having to open run..wait...close,
open another run...wait... etc.

I have no problems calling this "main" macro with the command
"Application.Run ("T1Seed1.xls!Main")", but this halts my macro.

Are you saying that there could be a "end" code line in there which is
halting it my macro? I haven't tried running another test macro that
I know doesn't have this type of code.
But in the mean time, do you know of any other way to do this?

Thanks for the help,

Jack

 

[Arnie]

That would be a good test to try (calling a known safe macro instead). What
I was wondering, tho, was if you really need to be calling T1Seed1!Main each
time or if you need to change the sheetname with each loop?

Other than that, you could try moving the Application.Run outside the loop.
Loop until all your sheets changes are made then call "Main" for each sheet?