Automatic Prompt for Users to Save
in [Programming]
|
From: sg on 3 Jun 2010 21:44 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.
From: Rich Locus on 4 Jun 2010 01:56 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.
From: sg on 4 Jun 2010 11:22 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.
From: Rich Locus on 5 Jun 2010 02:08 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 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 > > -- > 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.
From: Rich Locus on 5 Jun 2010 02:08 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 -- 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.
|
Next
|
Last
Pages: 1 2 Prev: using ADO from Excel 2007 Next: Vlookup in vba - how to use absolute rows not relative |