Macro to save query as Excel file

[Carl]

Please can anyone tell me how I can create a macro to save the results
of a query as an excel file? The query is called Student List and I
would like to save it to "My Documents".

We have had some problems with our database crashing. We would
therefore like another way to have access to basic student data and
thought we automatically save the results of this query when we close
Access.

Any suggestions on how we could do this would be really appreciated

Thanks a lot

Carl
Oxford UK

[Stewart Allen]

Hi Carl

Create a command button on your form (without using the wizard) and in the
On Click event type the following;

On Error Resume Next
DoCmd.OuptputTo, "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False
For more details look at OutputTo in the help file.

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83**************************@posting.google.c om...

Please can anyone tell me how I can create a macro to save the results
of a query as an excel file? The query is called Student List and I
would like to save it to "My Documents".

We have had some problems with our database crashing. We would
therefore like another way to have access to basic student data and
thought we automatically save the results of this query when we close
Access.

Any suggestions on how we could do this would be really appreciated

Thanks a lot

Carl
Oxford UK

[Carl]

Thanks Stewart. I couldn't get this to work but I was able to create a
macro and use the OutputTo action from there.

Please can you tell me if there is any way to automate this when a
user closes out of Access? And if so, does it matter if other users
have the database open at the same time?

TIA

Carl

"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message news:<bn**********@news.wave.co.nz>...

Hi Carl

Create a command button on your form (without using the wizard) and in the
On Click event type the following;

On Error Resume Next
DoCmd.OuptputTo, "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False
For more details look at OutputTo in the help file.

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83**************************@posting.google.c om...

Please can anyone tell me how I can create a macro to save the results
of a query as an excel file? The query is called Student List and I
would like to save it to "My Documents".

We have had some problems with our database crashing. We would
therefore like another way to have access to basic student data and
thought we automatically save the results of this query when we close
Access.

Any suggestions on how we could do this would be really appreciated

Thanks a lot

Carl
Oxford UK

[Stewart Allen]

Are there any error messages when using the code? Remove the "on error
resume next" line and see if any error messages pop up. The DoCmd.OutputTo
line is on one line, the newreader has wrapped the code to the next line. If
you want Excel to open the newly created file, change the False to True.

Yes there is a way to do this automatically when Access closes. If you have
a switchboard (which should be the first form to open) place the
DoCmd.OutputTo line in the Unload event of the switchboard form. If you have
several users opening the databse at the same time, consider splitting the
database into 2; one holds the tables only and is placed on a server or
dedicated PC, and every user has their own copy of the user interface on
their own PCs. These contains all the forms, queries, reports etc and have
linked tables to the backend. Check this link for more details
http://members.iinet.net.au/~allenbrowne/ser-01.html

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83*************************@posting.google.co m...

Thanks Stewart. I couldn't get this to work but I was able to create a
macro and use the OutputTo action from there.

Please can you tell me if there is any way to automate this when a
user closes out of Access? And if so, does it matter if other users
have the database open at the same time?

TIA

Carl

"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message

news:<bn**********@news.wave.co.nz>...

Hi Carl

Create a command button on your form (without using the wizard) and in the On Click event type the following;

On Error Resume Next
DoCmd.OuptputTo, "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False
For more details look at OutputTo in the help file.

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83**************************@posting.google.c om...

Please can anyone tell me how I can create a macro to save the results
of a query as an excel file? The query is called Student List and I
would like to save it to "My Documents".

We have had some problems with our database crashing. We would
therefore like another way to have access to basic student data and
thought we automatically save the results of this query when we close
Access.

Any suggestions on how we could do this would be really appreciated

Thanks a lot

Carl
Oxford UK

[Carl]

Hi Stewart,

I have split the database so that the back end sits on the server and
everything else is in "My Documents". I am trying to add the code to
the Switchboard Unload Event. From the Properties window I clicked
next to On Unload
and then on the button with the three dots. This took me to the VBA
editor and I pasted the following

On Error Resume Next
DoCmd.OuptputTo , "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False
into the function that had been created to give:(the False is on the
same line as the DoCmd)

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
DoCmd.OuptputTo , "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False

End Sub
I now get a Compile error:
Method or Data Member not found

when I try to close the database

Please can you offer any suggestions?

TIA
Carl

"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message news:<bn**********@news.wave.co.nz>...

Are there any error messages when using the code? Remove the "on error
resume next" line and see if any error messages pop up. The DoCmd.OutputTo
line is on one line, the newreader has wrapped the code to the next line. If
you want Excel to open the newly created file, change the False to True.

Yes there is a way to do this automatically when Access closes. If you have
a switchboard (which should be the first form to open) place the
DoCmd.OutputTo line in the Unload event of the switchboard form. If you have
several users opening the databse at the same time, consider splitting the
database into 2; one holds the tables only and is placed on a server or
dedicated PC, and every user has their own copy of the user interface on
their own PCs. These contains all the forms, queries, reports etc and have
linked tables to the backend. Check this link for more details
http://members.iinet.net.au/~allenbrowne/ser-01.html

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83*************************@posting.google.co m...

Thanks Stewart. I couldn't get this to work but I was able to create a
macro and use the OutputTo action from there.

Please can you tell me if there is any way to automate this when a
user closes out of Access? And if so, does it matter if other users
have the database open at the same time?

TIA

Carl

"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message

news:<bn**********@news.wave.co.nz>...

Hi Carl

Create a command button on your form (without using the wizard) and in the On Click event type the following;

On Error Resume Next
DoCmd.OuptputTo, "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False
For more details look at OutputTo in the help file.

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83**************************@posting.google.c om...
> Please can anyone tell me how I can create a macro to save the results
> of a query as an excel file? The query is called Student List and I
> would like to save it to "My Documents".
>
> We have had some problems with our database crashing. We would
> therefore like another way to have access to basic student data and
> thought we automatically save the results of this query when we close
> Access.
>
> Any suggestions on how we could do this would be really appreciated
>
> Thanks a lot
>
> Carl
> Oxford UK

[Stewart Allen]

This sounds like a referencing problem. What version of access are you
using? While in the VBA editor goto Tools>References and list all the items
that are ticked. Also while you're in the editor goto Debug and click
Compile. What is the line of code that is highlighted in yellow? Copy and
paste that block of code into your reply message and indicate which line was
highlighted.

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83**************************@posting.google.c om...

Hi Stewart,

I have split the database so that the back end sits on the server and
everything else is in "My Documents". I am trying to add the code to
the Switchboard Unload Event. From the Properties window I clicked
next to On Unload
and then on the button with the three dots. This took me to the VBA
editor and I pasted the following

On Error Resume Next
DoCmd.OuptputTo , "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False
into the function that had been created to give:(the False is on the
same line as the DoCmd)

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
DoCmd.OuptputTo , "Student List", acFormatXLS, "C:\My
Documents\Students.xls", False

End Sub
I now get a Compile error:
Method or Data Member not found

when I try to close the database

Please can you offer any suggestions?

TIA
Carl

"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message

news:<bn**********@news.wave.co.nz>...

Are there any error messages when using the code? Remove the "on error
resume next" line and see if any error messages pop up. The DoCmd.OutputTo line is on one line, the newreader has wrapped the code to the next line. If you want Excel to open the newly created file, change the False to True.

Yes there is a way to do this automatically when Access closes. If you have a switchboard (which should be the first form to open) place the
DoCmd.OutputTo line in the Unload event of the switchboard form. If you have several users opening the databse at the same time, consider splitting the database into 2; one holds the tables only and is placed on a server or
dedicated PC, and every user has their own copy of the user interface on
their own PCs. These contains all the forms, queries, reports etc and have linked tables to the backend. Check this link for more details
http://members.iinet.net.au/~allenbrowne/ser-01.html

Stewart
"Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
news:83*************************@posting.google.co m...

Thanks Stewart. I couldn't get this to work but I was able to create a
macro and use the OutputTo action from there.

Please can you tell me if there is any way to automate this when a
user closes out of Access? And if so, does it matter if other users
have the database open at the same time?

TIA

Carl

"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message

news:<bn**********@news.wave.co.nz>...

> Hi Carl
>
> Create a command button on your form (without using the wizard) and in

the

> On Click event type the following;
>
> On Error Resume Next
> DoCmd.OuptputTo, "Student List", acFormatXLS, "C:\My
> Documents\Students.xls", False
>
>
> For more details look at OutputTo in the help file.
>
> Stewart
>
>
> "Carl" <ca*********@oxmhc-tr.nhs.uk> wrote in message
> news:83**************************@posting.google.c om...
> > Please can anyone tell me how I can create a macro to save the

results > > of a query as an excel file? The query is called Student List and I > > would like to save it to "My Documents".
> >
> > We have had some problems with our database crashing. We would
> > therefore like another way to have access to basic student data and > > thought we automatically save the results of this query when we close > > Access.
> >
> > Any suggestions on how we could do this would be really appreciated > >
> > Thanks a lot
> >
> > Carl
> > Oxford UK