Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Opening an AutoExecute Macro Workbook from an AutoExecute Macro Workbook

You can't close the calling program. Code can't just halt and disappear -
at least it is not designed to behave this way.

What you would have to do is put your code in the compprint.xls in a normal
module

Public Sub MyPrintCode()
dim wkbk as workbook
on error resume next
set wkbk = Workbooks("Generator.xls")
On error goto 0
if wkbk is nothing then
Workbooks.Open "C:\Generator.xls"
End if
' now wkbk holds a reference to the open Generator.xls

' wkbk.worksheets(Array("Sheet1","Sheet2")).Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.count)
wkbk.Close Savechanges:=false
End Sub

in the workbook.open event of CompPrint.xls you would have

Private Sub Workbooks_Open()
MyPrintCode
End Sub

in the generator program you could do this

Application.EnableEvent = False
' open comprint, but don't run workbook_open code
workbooks.Open "C:\comprint.xls"
Application.EnableEvents = True

' execute the print code in compprint
Application.Ontime now(), "Comprint.xls!MyPrintcode"
End sub

Using application.Ontime breaks the code chain between the two workbooks
which you have now.

Regards,
Tom Ogilvy


"John" wrote in message
om...
I have two Excel Workbooks that are both coded with VBA and are set to
AutoExecute. The first program generates an output. The second is
the Print Program. I give the user an option in the generation
program if they would like to execute the print program upon
completion. Here I would like to call the print macro.

When I do this by opening the print workbook (Workbooks.open
filename:="CompPrint.xls") the print workbook macro opens, executes
through the code, but does not have the ability to open or close
worksheets. It is powerless. (When I execute the print program
alone, it executes fine).

The print program needs to be run as a stand alone as well as a called
program from the generator (this is why I do not want to save it as a
module and put it into the generator code -- This would make
maintenance a bear).

The flow I am looking for is the Generator program to execute to the
point where it calls (opens) the Print program. The Print program
then pulls a few cells of information from the open Generator sheet
and then should close the calling generator program. (When the Print
program is called from the Generator program, it does NOT have the
power to close the sheet or open any others for that matter). Then
the printer program should run to completion.

Any help would be greatly appreciated. Thank you so much for you help
in advance.

John Germani



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Opening a Workbook with a Macro Eli[_4_] Excel Discussion (Misc queries) 4 October 3rd 08 12:13 AM
Autoexecute macro Joanne M. Excel Discussion (Misc queries) 3 June 15th 07 03:09 PM
HELP !! Macros to autoexecute on open Wins07 Excel Discussion (Misc queries) 2 April 4th 07 10:16 PM
how do i run an autoexecute macro in excel robin morris Excel Worksheet Functions 2 July 6th 06 04:36 PM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"