close/open workbook without prompts

[HammerJoe]

Hi,

I have a macro in my main workbook that creates a new workbook and it
copies from main workbook a report sheet and pastes only formats and
values to it so it can be emailed.

Because people can send it as an attachment, I decided to create a new
workbook with just one sheet. It makes the file smaller and at the
same time it removes the formulas from prying eyes.

The problem is I can't use the sendmail function, because it requires
saving the new workbook to the disk (as far as I know) and because of
profile space limitations this is not workable for me).

The other problem, when the workbook is created a button is created to
allow the user to close the workbook and return to the main workbook.
That works fine except when the email recipient opens the file.

there is a prompt about broken links (which is obviously because of
the button).
I understand that it is not possible to avoid that prompt with excel
2003

So I decided to get rid of the button and just wait for the new
workbook to be closed before returning to the main workbook and
continuing with the macro
Another problem then, excel of course prompts to save the new
workbook.


Can I eliminate the Broken/Update link prompt when opening the
workbook?

OR

How can I close the workbook without prompt??
(i am not sre if I can inject code from a macro into the new workbook
to detect close event??)

 

[Noman]

Try
Dim app As New Microsoft.Office.Interop.Excel.Application
app.DisplayAlerts = False
' Do your work
app.DisplayAlerts = True

 

[HammerJoe]

Try
Dim app As New Microsoft.Office.Interop.Excel.Application
app.DisplayAlerts = False
' Do your work
app.DisplayAlerts = True

Where do you put that?

This is a new workbook that is created by a macro using with the
Workbook.Add, so no code is added to the newly workbook.
When it is closed it doesnt call any events in the main workbook.
The only way I have found so far to detect that the new workbook has
been closed is during mainworkbook.activate procedure tries to put the
focus back to it and of course an error occurs and is trapped.

More sugestions or alternatives?

 

[Doug Glancy]

HammerJoe,

You could add code to the new workbook at runtime. See
http://www.cpearson.com/excel/vbe.aspx. I don't think I'd do it that way.

You could also just create an Excel template with the code in it and open it
like this:
Workbooks.Open (ThisWorkbook.Path & "\MyTemplate.xlt")
Of course then you'd be sending out a workbook with macros in it, but I
guess you could consult Chip's page above for how to remove it.

Here's one more possibility - in the code for your main workbook make a
class that "controls" the workbook. First, insert a class to your workbook
in the VBE. In the code module for that new class paste this code:

Option Explicit
Private WithEvents wbNewBook As Workbook
Private m_WB As Workbook

Private Sub Class_Terminate()
Set m_WB = Nothing
Set wbNewBook = Nothing
End Sub

Public Property Set Wb(PassedWb As Workbook)
Set m_WB = PassedWb
Set wbNewBook = m_WB
End Property

Public Property Get Wb() As Workbook
Set Wb = m_WB
End Property

Private Sub wbNewBook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
m_WB.Close savechanges:=False
Workbooks("test.xls").Activate
Call ContinueOnOurMerryWay
Application.EnableEvents = True
Cancel = True
End Sub

Private Sub wbNewBook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
MsgBox "Sorry, changes not allowed"
Cancel = True
End Sub

Private Sub wbNewBook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'this is not necessary for your purposes
'but shows how this class captures all
'events in your new book, just fyi
MsgBox "selection change"
End Sub


Then add a regular module, which uses Class1. (You can rename the class
from the default of "Class1" to whatever you want.)

Option Explicit
Public NewBook As Class1

Sub StartTheBallRolling()

ThisWorkbook.Sheets(1).Copy
Set NewBook = New Class1
Set NewBook.Wb = ActiveWorkbook
End Sub

Sub ContinueOnOurMerryWay()
Set NewBook.Wb = Nothing
Set NewBook = Nothing
MsgBox "Continuing"
End Sub

My understanding is that you never want the user to save the workbook. If
that's not true then the above is wrong. If it's true, and you decide to go
the template route above, you can use the BeforeClose and BeforeSave code
above. Just change "m_WB.Close" to "ThisWorkBook.Close"

hth,

Doug

 

[HammerJoe]

This is brilliant, thanks Doug.

 

[Doug Glancy]

I'm glad you like it! I've just recently really understood how powerful
classes are in this regard.

Doug