By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Exporting a table using a variable file name

P: 2
Apologies if this is a duplicate thread, didn't come across the answer after searching.

As part of the database closing process, I'd like to add in code that automatically exports my main data table (tbl_aggregatedata) as an Excel file onto the users C: drive. My hope is that I could have the file name use the current date so that a series of saved data points could exist should the user do something to the data in the database. My goal would be to have the file save as something like 112006databkup.xls. Is this possible as an event to be run from a command button?

Thanks
Nov 20 '06 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,492
I believe so.

You can also trigger it with the OnClose of your main form if you prefer.
Look in help for 'DoCmd.TransferSpreadsheet' for how to export your data.
Nov 20 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Apologies if this is a duplicate thread, didn't come across the answer after searching.

As part of the database closing process, I'd like to add in code that automatically exports my main data table (tbl_aggregatedata) as an Excel file onto the users C: drive. My hope is that I could have the file name use the current date so that a series of saved data points could exist should the user do something to the data in the database. My goal would be to have the file save as something like 112006databkup.xls. Is this possible as an event to be run from a command button?

Thanks
Okay there are a couple of issues here. The more complicated one first.

In Access there is no way to automatically trigger an event on application close. However, there is a workaround on this. Create an unbound form lets call it frmStartup. Set this as the startup form rather than your current form (e.g. Switchboard). Set the properties of this form to invisible so the user never sees it.

Now you have to code three form events.

1. The On Load event

Expand|Select|Wrap|Line Numbers
  1.  Private sub Form_Load() 
  2.  
  3.   ' open the normal startup form
  4.   DoCmd.OpenForm "Switchboard"
  5.  
  6. End Sub
  7.  
2. The On Unload event

Expand|Select|Wrap|Line Numbers
  1.  Private sub Form_UnLoad(Cancel As Integer) 
  2. Dim rslt As Integer
  3.  
  4.   'prompt the user that the application is closing
  5.   rslt = Msgbox("The database is closing down", vbOKCancel)
  6.   If rslt = vbCancel Then
  7.     Cancel = True
  8.   Else
  9.     ' code to export main table
  10.      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "tbl_aggregatedata", & _
  11.     "C:\" & Month(Date) & Year(Date) & "databkup.xls", True
  12.   End If
  13.  
  14. End Sub
  15.  
3. Form On Close event (in case form is accidently closed)

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_Close(Cancel As Integer) 
  2.  
  3.   Form_UnLoad
  4.  
  5. End Sub
  6.  
Nov 20 '06 #3

NeoPa
Expert Mod 15k+
P: 31,492
Nice answer.
You also have the option of cancelling the close if you prefer by setting
Expand|Select|Wrap|Line Numbers
  1. Cancel = True
Nov 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Nice answer.
You also have the option of cancelling the close if you prefer by setting
Expand|Select|Wrap|Line Numbers
  1. Cancel = True
Actually that is a better idea.

tallman21

replace my line in the On Close event

Expand|Select|Wrap|Line Numbers
  1. Form_Unload
with Adrian's

Expand|Select|Wrap|Line Numbers
  1.  Cancel=True
Nov 20 '06 #5

P: 2
Thank you, thank you. A thousand times thank you. That worked like a charm and does exactly what I need.
Nov 21 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you, thank you. A thousand times thank you. That worked like a charm and does exactly what I need.
That's great.

I'm glad we could help.

Mary
Nov 21 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.