VBA Help Needed

I created a workbook and saved it as a template to be used repeatedly. It
uses a query to select and extract data from an external database. The
query refreshes automatically when the workbook is opened. I recorded a
macro to save this subset of data as a database file (dBase4) and set it to
save before the workbook is closed. It works OK, but I would like make it
even better. How can I supress the two questions that pop up?

"Do you want to replace the existing file?" (yes).
"Do you want to save changes to ups.dbf?" (no).

Here is the macro:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ChDir "S:\"
ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False
End Sub

--
Carlos

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ChDir "S:\"

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False
Application.DisplayAlerts = True

End Sub



From VBA Help File
DisplayAlerts Property
True if Microsoft Excel displays certain alerts and messages while a macro
is running. Read/write Boolean.
Remarks
The default value is True. Set this property to False if you don't want to
be disturbed by prompts and alert messages while a macro is running; any
time a message requires a response, Microsoft Excel chooses the default
response.

If you set this property to False, Micorosoft Excel sets this property to
True when the code is finished, unless you are running cross process code.

When using the SaveAs method for workbooks to overwrite an existing file,
the 'Overwrite' alert has a default of 'No', while the 'Yes' response is
selected by Excel when the DisplayAlerts property is set equal to True.







"CarlosAntenna" <nunayo@binnez.com> wrote in message
news:%23SFIKmM$EHA.3592@TK2MSFTNGP09.phx.gbl...
>I created a workbook and saved it as a template to be used repeatedly. It
> uses a query to select and extract data from an external database. The
> query refreshes automatically when the workbook is opened. I recorded a
> macro to save this subset of data as a database file (dBase4) and set it
> to
> save before the workbook is closed. It works OK, but I would like make it
> even better. How can I supress the two questions that pop up?
>
> "Do you want to replace the existing file?" (yes).
> "Do you want to save changes to ups.dbf?" (no).
>
> Here is the macro:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ChDir "S:\"
> ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
> CreateBackup:=False
> End Sub
>
> --
> Carlos
>
>

Thanks, Paul,

That takes care of the first one (overwrite). I still get the second one.
I think that one is triggered by closing the worksheet rather than from
within the macro. Can the macro also close the workbook while
DisplayAlerts=False?

--
Carlos

"Paulw2k" <paulw2k@hotmail.com> wrote in message
news:cshdk1$c1f$1@news6.svr.pol.co.uk...
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> ChDir "S:\"
>
> Application.DisplayAlerts = False
>
> ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
> CreateBackup:=False
> Application.DisplayAlerts = True
>
> End Sub
>
>
>
> From VBA Help File
> DisplayAlerts Property
> True if Microsoft Excel displays certain alerts and messages while a macro
> is running. Read/write Boolean.
> Remarks
> The default value is True. Set this property to False if you don't want to
> be disturbed by prompts and alert messages while a macro is running; any
> time a message requires a response, Microsoft Excel chooses the default
> response.
>
> If you set this property to False, Micorosoft Excel sets this property to
> True when the code is finished, unless you are running cross process code.
>
> When using the SaveAs method for workbooks to overwrite an existing file,
> the 'Overwrite' alert has a default of 'No', while the 'Yes' response is
> selected by Excel when the DisplayAlerts property is set equal to True.
>
>
>
>
>
>
>
> "CarlosAntenna" <nunayo@binnez.com> wrote in message
> news:%23SFIKmM$EHA.3592@TK2MSFTNGP09.phx.gbl...
> >I created a workbook and saved it as a template to be used repeatedly.
It
> > uses a query to select and extract data from an external database. The
> > query refreshes automatically when the workbook is opened. I recorded a
> > macro to save this subset of data as a database file (dBase4) and set it
> > to
> > save before the workbook is closed. It works OK, but I would like make
it
> > even better. How can I supress the two questions that pop up?
> >
> > "Do you want to replace the existing file?" (yes).
> > "Do you want to save changes to ups.dbf?" (no).
> >
> > Here is the macro:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > ChDir "S:\"
> > ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
> > CreateBackup:=False
> > End Sub
> >
> > --
> > Carlos
> >
> >
>
>

This would kind of scare me--if I made changes that I really wanted to save and
then suppressed that prompt.

I think I'd move the save as .dbf to an "on demand" macro--not have it called
from _beforeclose.

But this did work ok for me:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False
Application.DisplayAlerts = True
Me.Saved = True
End Sub

(There's no reason to change to a different drive if you specify the path in the
..SaveAs line.)

CarlosAntenna wrote:
>
> Thanks, Paul,
>
> That takes care of the first one (overwrite). I still get the second one.
> I think that one is triggered by closing the worksheet rather than from
> within the macro. Can the macro also close the workbook while
> DisplayAlerts=False?
>
> --
> Carlos
>
> "Paulw2k" <paulw2k@hotmail.com> wrote in message
> news:cshdk1$c1f$1@news6.svr.pol.co.uk...
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > ChDir "S:\"
> >
> > Application.DisplayAlerts = False
> >
> > ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
> > CreateBackup:=False
> > Application.DisplayAlerts = True
> >
> > End Sub
> >
> >
> >
> > From VBA Help File
> > DisplayAlerts Property
> > True if Microsoft Excel displays certain alerts and messages while a macro
> > is running. Read/write Boolean.
> > Remarks
> > The default value is True. Set this property to False if you don't want to
> > be disturbed by prompts and alert messages while a macro is running; any
> > time a message requires a response, Microsoft Excel chooses the default
> > response.
> >
> > If you set this property to False, Micorosoft Excel sets this property to
> > True when the code is finished, unless you are running cross process code.
> >
> > When using the SaveAs method for workbooks to overwrite an existing file,
> > the 'Overwrite' alert has a default of 'No', while the 'Yes' response is
> > selected by Excel when the DisplayAlerts property is set equal to True.
> >
> >
> >
> >
> >
> >
> >
> > "CarlosAntenna" <nunayo@binnez.com> wrote in message
> > news:%23SFIKmM$EHA.3592@TK2MSFTNGP09.phx.gbl...
> > >I created a workbook and saved it as a template to be used repeatedly.
> It
> > > uses a query to select and extract data from an external database. The
> > > query refreshes automatically when the workbook is opened. I recorded a
> > > macro to save this subset of data as a database file (dBase4) and set it
> > > to
> > > save before the workbook is closed. It works OK, but I would like make
> it
> > > even better. How can I supress the two questions that pop up?
> > >
> > > "Do you want to replace the existing file?" (yes).
> > > "Do you want to save changes to ups.dbf?" (no).
> > >
> > > Here is the macro:
> > >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > ChDir "S:\"
> > > ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
> > > CreateBackup:=False
> > > End Sub
> > >
> > > --
> > > Carlos
> > >
> > >
> >
> >

--

Dave Peterson

Thanks Dave,

It works great. Not scary because the data is coming from a query that only
takes a second or two to run. I can always run it again if something goes
wrong.

I thought the ChDir was redundant, but I didn't code this macro. I recorded
it. Thanks for confirming that.

--
Carlos
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41EC4AF5.3ADD5F3D@netscapeXSPAM.com...
> This would kind of scare me--if I made changes that I really wanted to
> save and
> then suppressed that prompt.
>
> I think I'd move the save as .dbf to an "on demand" macro--not have it
> called
> from _beforeclose.
>
> But this did work ok for me:
>
> Option Explicit
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Application.DisplayAlerts = False
> ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
> CreateBackup:=False
> Application.DisplayAlerts = True
> Me.Saved = True
> End Sub
>
> (There's no reason to change to a different drive if you specify the path
> in the
> .SaveAs line.)
>
> CarlosAntenna wrote:
>>
>> Thanks, Paul,
>>
>> That takes care of the first one (overwrite). I still get the second
>> one.
>> I think that one is triggered by closing the worksheet rather than from
>> within the macro. Can the macro also close the workbook while
>> DisplayAlerts=False?
>>
>> --
>> Carlos
>>
>> "Paulw2k" <paulw2k@hotmail.com> wrote in message
>> news:cshdk1$c1f$1@news6.svr.pol.co.uk...
>> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> > ChDir "S:\"
>> >
>> > Application.DisplayAlerts = False
>> >
>> > ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4, _
>> > CreateBackup:=False
>> > Application.DisplayAlerts = True
>> >
>> > End Sub
>> >
>> >
>> >
>> > From VBA Help File
>> > DisplayAlerts Property
>> > True if Microsoft Excel displays certain alerts and messages while a
>> > macro
>> > is running. Read/write Boolean.
>> > Remarks
>> > The default value is True. Set this property to False if you don't want
>> > to
>> > be disturbed by prompts and alert messages while a macro is running;
>> > any
>> > time a message requires a response, Microsoft Excel chooses the default
>> > response.
>> >
>> > If you set this property to False, Micorosoft Excel sets this property
>> > to
>> > True when the code is finished, unless you are running cross process
>> > code.
>> >
>> > When using the SaveAs method for workbooks to overwrite an existing
>> > file,
>> > the 'Overwrite' alert has a default of 'No', while the 'Yes' response
>> > is
>> > selected by Excel when the DisplayAlerts property is set equal to True.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "CarlosAntenna" <nunayo@binnez.com> wrote in message
>> > news:%23SFIKmM$EHA.3592@TK2MSFTNGP09.phx.gbl...
>> > >I created a workbook and saved it as a template to be used repeatedly.
>> It
>> > > uses a query to select and extract data from an external database.
>> > > The
>> > > query refreshes automatically when the workbook is opened. I
>> > > recorded a
>> > > macro to save this subset of data as a database file (dBase4) and set
>> > > it
>> > > to
>> > > save before the workbook is closed. It works OK, but I would like
>> > > make
>> it
>> > > even better. How can I supress the two questions that pop up?
>> > >
>> > > "Do you want to replace the existing file?" (yes).
>> > > "Do you want to save changes to ups.dbf?" (no).
>> > >
>> > > Here is the macro:
>> > >
>> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> > > ChDir "S:\"
>> > > ActiveWorkbook.SaveAs Filename:="S:\ups.dbf", FileFormat:=xlDBF4,
>> > > _
>> > > CreateBackup:=False
>> > > End Sub
>> > >
>> > > --
>> > > Carlos
>> > >
>> > >
>> >
>> >
>
> --
>
> Dave Peterson