Automatic Prompt for Users to Save

[sg]

I have an Excel 2007 workbook that is populated by an Access table. Once the
data is transferred to Excel, Access opens Excel so we can view the data. I
would like to have the Excel spreadsheet then prompt the users to save so
they don't forget. I tried to just set up the Excel spreadsheet as a
template, but get an error when Excel opens from Access and then the data
doesn't transfer from Access. Any ideas on how I can do this? Thanks in
advance.

 

[Rich Locus]

Hello:
I could not tell from your post if the Excel worksheet was pre-existing or
created by Access,

If it is pre-existing, you can add a module to alert the user when the file
opens, or save the file when they close it.

Add one of these modules:

Option Explicit

Public Sub Auto_Open()
MsgBox ("Don't Forget to Save Your File")
End Sub

Or

Public Sub Auto_Close()
.... Put Code to Save The File Here
End Sub

These are activated by Opening an Excel spreadsheet or closing it.

 

[sg]

Sorry I wasn't a little more specific about the spreadsheet. It is a
pre-existing spreadsheet. I appreciate your feedback, but I am really hoping
to get the Save As dialog box to open so they are more likely to save it with
a new name. If I just give them a reminder, they may just hit Save and I
don't want for this one to be overridden.

I wanted to set it up as a template so when they save, it prompts them for a
file name, but something in the transfer of data from Access to Excel doesn't
work correctly when I try to transfer to a template file. The data doesn't
actually get moved from Access to Excel.

 

[Rich Locus]

Hello Again:
You should probably also update the message to say "Use Save As with a
Different File Name."

 

[Rich Locus]

Hello:
Ahhh... I see what you want.

Here's the solution (please click "Yes" to This answered by question".

Get into the VBA editor.

Double click on "ThisWorksheet". This is in the Worksheet area, not in the
Module area.
Then paste in this Procedure:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveAsUI = False Then

Cancel = True

MsgBox "You cannot save this workbook. Use Save As"

End If

End Sub

 

[Gord Dibben]

If you save the original as a true Template(*.xlt or *.xltx) the template
does not open, just a copy of it.

So the Template itself never gets overwritten.

The copy that opens has to be saved with a new name.

No code necessary.


Gord Dibben MS Excel MVP

 

[Rich Locus]

Gord:
I saw your post and that would have been my suggestion, except that the
"SG", the one who asked the question said this: "I tried to just set up the
Excel spreadsheet as a template, but get an error when Excel opens from
Access and then the data doesn't transfer from Access".
So apparently there is some issue with the template approach.

 

[sg]

Thanks for getting back to me again. I hate to sound like I don't know what
I'm doing, but I guess I don't in this case...

I don't see what you mean by double-clicking on "ThisWorksheet". Can you
help me to get to the right place? Thanks!

 

[Gord Dibben]

Alt + F11 to open the VBE

CTRL + r to open Project Explorer.

Find your workbook/project by name.

Click on the + to expand it.

Click the + on Microsoft Excel Objects to expand that.

Double-click on ThiisWorkbook to open.

Paste the code in there.


Gord Dibben MS Excel MVP