VBA Help Needed

  • Thread starter Thread starter CarlosAntenna
  • Start date Start date
C

CarlosAntenna

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
 
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.
 
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 said:
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.
Click to expand...
 
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.)
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?
Click to expand...
 
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.
 
Back
Top