Automatic Prompt for Users to Save
in [Programming]
|
From: Gord Dibben on 5 Jun 2010 10:46 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 <sg(a)discussions.microsoft.com> 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 20:43 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 <sg(a)discussions.microsoft.com> 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: sg on 6 Jun 2010 23:39 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 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: Gord Dibben on 7 Jun 2010 15:34 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 <sg(a)discussions.microsoft.com> 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 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.
First
|
Prev
|
Pages: 1 2 Prev: using ADO from Excel 2007 Next: Vlookup in vba - how to use absolute rows not relative |