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.

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.

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.

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.

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.

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 <(E-Mail Removed)> 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 itwith
>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 thefile
>> 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 thedata
>> > doesn't transfer from Access. Any ideas on how I can do this? Thanks in
>> > advance.

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 <(E-Mail Removed)> 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.
>
> .
>

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.

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 <(E-Mail Removed)> 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 whenthe 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 spreadsheetas a
>> > > > template, but get an error when Excel opens from Access and thenthe data
>> > > > doesn't transfer from Access. Any ideas on how I can do this? Thanks in
>> > > > advance.