Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
Hello Again:
You should probably also update the message to say "Use Save As with a Different File Name." -- Rich Locus Logicwurks, LLC "Rich Locus" wrote: 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 Procedu 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 -- Rich Locus Logicwurks, LLC "sg" wrote: 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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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 Procedu 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 -- Rich Locus Logicwurks, LLC "sg" wrote: 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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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 On Fri, 4 Jun 2010 08:22:39 -0700, sg wrote: 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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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. -- Rich Locus Logicwurks, LLC "Gord Dibben" wrote: 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 On Fri, 4 Jun 2010 08:22:39 -0700, sg wrote: 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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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! "Rich Locus" wrote: 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 Procedu 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 -- Rich Locus Logicwurks, LLC "sg" wrote: 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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Prompt for Users to Save
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 On Sun, 6 Jun 2010 20:39:44 -0700, sg wrote: 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! "Rich Locus" wrote: 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 Procedu 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 -- Rich Locus Logicwurks, LLC "sg" wrote: 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" wrote: 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. -- Rich Locus Logicwurks, LLC "sg" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
prompt user to save file as {desired_name} and save it to a variab | Excel Programming | |||
vba pw prompt undesired for end users | Excel Programming | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
How do I set up a prompt to users in an excel template? | Excel Discussion (Misc queries) | |||
Excel prompt inaccessible to users | Excel Discussion (Misc queries) |